Getting Started

This document describes three add-ins to the OLAP Manager in Microsoft® SQL Server™ version 7.0 OLAP Services. Collectively, these three add-ins are called the “OLAP Manager Add-in Kit 1.” These add-ins enable you to use the OLAP Manager to:

  • Archive and restore OLAP Services databases.
  • Create and maintain calculated members in virtual cubes.
  • Copy and paste objects in the OLAP Manager tree view.

This document complements OLAP Services Books Online (msdssbol.chm), and it is assumed that readers are familiar with the topics therein.

For more information about this document, its use, and other resources, see the following topics:

  • Documentation Conventions
  • SQL Server 7.0 Copyright and Disclaimer
  • Using the HTML Help Viewer
  • Using Help
  • Getting Help from Microsoft Technical Support
  • Finding Information on the Internet
  • Accessibility for People with Disabilities

For more information about the add-ins, select from the following topics:

  • Introducing Add-ins
  • Installing the Add-ins
  • Using the Add-ins
    • Archive and Restore Databases Add-in
    • Calculated Member Manager Add-in
    • Copy and Paste Objects Add-in

(c) 1999 Microsoft Corporation. All Rights Reserved.


Documentation Conventions

This document uses the following conventions to distinguish elements of text.

Convention | Used for
UPPERCASE | Transact-SQL keywords.
Initial Capitals | Paths and file names.
Bold | Database names, table names, column names, stored procedures, command-prompt utilities, menus, commands, dialog box options, and text that must be typed exactly as shown.
Italic | User-supplied variables.
Monospace | Code samples, examples, display text, and error messages.


SQL Server 7.0 Copyright and Disclaimer

Information in this document is subject to change without notice. The example companies, organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Ó 1999 Microsoft Corporation. All rights reserved.

Microsoft, MS-DOS, MS, Windows, Windows NT, ActiveX, and Visual Basic are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.


Using the HTML Help Viewer

Microsoft® SQL Server™ OLAP Services documentation is displayed using the HTML Help Viewer.

Using Full-text Search

A basic search of Help topics consists of the word or phrase you want to find. Several options for refining your search are available:

  • Using words, phrases, and wildcards
  • Using operators: AND, OR, NOT, and NEAR
  • Using nested expressions

Some guidelines apply when performing a full-text search:

  • Searches are case-insensitive; you can type your search in either uppercase or lowercase characters.
  • Punctuation marks, such as the period (.), colon (:), semicolon (;), comma (,), and hyphen (-), are ignored.
  • Group the elements of your search using double quotation marks or parentheses. If you are searching for a file name with an extension, group the entire string in double quotation marks, for example, “filename.ext”. Otherwise, the search will treat the period as an AND. The period is a delimiter that breaks the file name into two terms.

To search topics and topic titles using full-text search

  1. In the navigation pane of the HTML Help Viewer, click the Search tab, and then type the word or phrase to find.
    • To search in topic titles only, select Search titles only.
    • To refine a search to include only the last group of topics you searched, select Search previous results.
    • To add Boolean operators to your search, click the right-arrow button.
  2. Click List Topics, select the topic, and then click Display.
    • To sort the topic list, click Title, Location, or Rank.

Words, Phrases, and Wildcards

You can search for words or phrases and use wildcard expressions. This table displays the results of these different kinds of searches.

Search for | Example | Result
Single word | select | Topics that contain the word select. (You will also find grammatical variations, such as selector and selection.)
Phrase | “new operator” Or new operator | Topics that contain the literal phrase “new operator” and all its grammatical variations. Without the quotation marks, the query is equivalent to specifying “new AND operator,” which finds topics containing both of the individual words, instead of the phrase.
Wildcard expressions | esc* | Topics that contain the terms ESC, escape, escalation, and so on. The asterisk cannot be the only character in the term.
 | 80?86 | Topics that contain the terms 80186, 80286, 80386, and so on. The question mark cannot be the only character in the term.
 | *86 | Topics that contain the terms 386, 486, x86, QEMM386, 8086, and so on. The asterisk cannot be the only character in the term.

Note Select Match similar words to include grammatical variations for the word(s) on which you search. For example, a search on the word add finds add, adds, and added.

Operators: AND, OR, NOT, and NEAR

The AND, OR, NOT, and NEAR operators allow you to refine your search. If no operator is specified, AND is used. For example, the query “spacing border printing” is equivalent to “spacing and border and printing.”

Search for | Example | Result
Both terms in the same topic | dib AND palette | Topics containing both the words dib and palette.
Either term in a topic | raster OR vector | Topics containing either the word raster or the word vector.
First term without the second term | ole NOT dde | Topics containing the word OLE, but not the word DDE.
Both terms in the same topic, close together | user NEAR kernel | Topics containing the word user within eight words of the word kernel.

Note The |, &, and ! characters do not work as Boolean operators (use OR, AND, and NOT).

Nested Expressions

Nesting allows you to create more complex search expressions. For example, “control AND ((active OR dde) NEAR window)” finds topics containing the term control along with the terms active and window close together, or containing control along with the terms dde and window close together.

Some guidelines apply when searching Help topics using nested expressions:

  • Use parentheses to nest expressions within a query. The expressions in parentheses are evaluated before the rest of the query.
  • Searches that do not contain a nested expression are evaluated from left to right. For example, “Control NOT active OR dde” finds topics containing the term control without the term active, or topics containing the term dde. (However, “control NOT (active OR dde)” finds topics containing the term control without either of the terms active or dde.)
  • You cannot nest expressions more than five levels deep.

Other Features of the HTML Help Viewer

To find information in a Help topic

These are the tabs available in the navigation pane of the HTML Help Viewer for finding a Help topic:

  • To browse through topics by entries in the table of contents, click the Contents tab.
  • To see a list of index entries, click the Index tab, and then either type a word or scroll through the list. Similar to the index in a printed book, topics are often indexed under more than one entry.
  • To locate every occurrence of a word or phrase that may be contained in a Help topic, click the Search tab. You can narrow your search by defining subsets of topics that you specify.
  • To add a topic to a list of frequently used topics, display the topic you want to add, click the Favorites tab, and then click Add.

To navigate through previously viewed topics

  • To display the previous Help topic listed in the table of contents, click Previous.
  • To display the next Help topic listed in the table of contents, click Next.
  • To display the last previously viewed Help topic, click Back.
  • To display the next Help topic in a previously displayed sequence of topics, click Forward.
  • To display the Home page topic for the Help file you are viewing, click Home.

Note The HTML Help Viewer toolbar may not contain all the above options for moving through topics.

To link to other Help topics

• In a Help topic, click colored, underlined words to see other information related to the topic.

To copy and paste a Help topic

  1. In the topic pane of the HTML Help Viewer, right-click anywhere in the topic, click Select All, right-click again, and then click Copy.

    This copies the topic to the Clipboard.

  2. Open the document into which you want the topic copied.
  3. Position the cursor where you want the information to appear, and then on the Edit menu, click Paste.

Note To copy only part of a topic, select the part that you want to copy, right-click your selection, and then click Copy.

To print a Help topic

• In the topic pane of the HTML Help Viewer, right-click anywhere in the topic, and then click Print.

Note If you print from the Contents tab, you will see options for printing either the current topic or all of the topics under the selected heading.

To hide or show the HTML Help Viewer navigation pane

  • To hide the navigation pane, that contains the table of contents, index, search, and favorites list, click Hide. Only the topic pane will be displayed.
  • To display the navigation pane, click Show.

Note If you close the HTML Help Viewer, it will reopen using the last size, position, and navigation pane settings you selected.

To change the color of the HTML Help Viewer topic pane

  1. In Microsoft® Internet Explorer version 4.0, on the View menu, click Internet Options.
  2. On the General tab, click Colors.
  3. In the Colors dialog box, select the options you want, and then click OK.
  4. On the General tab, click Accessibility.
  5. Under Formatting, select the check boxes for the options you want, and then click OK.
  6. To apply the new color settings, in the Internet Options dialog box, click OK.

Note This only changes the color of the HTML Help Viewer topic pane.

To change the font size in the HTML Help Viewer topic pane

• On the Help toolbar, click Font.

Note This only changes the font in the HTML Help Viewer topic pane.

To use accessibility shortcut keys in the HTML Help Viewer

The following keyboard shortcuts can be used for navigation in the HTML Help Viewer version 1.2.

Action | Shortcut
Close the HTML Help Viewer | ALT+F4
Switch between the HTML Help Viewer and other open windows | ALT+TAB
Display the Options menu | ALT+O
Hide or show the navigation pane | ALT+O, and then press T
Print a topic | ALT+O, and then press P
Refresh the topic that appears in the topic pane | ALT+O, and then press R
Move back to the previous topic | ALT+O, and then press B
Move forward to the next topic (provided you have viewed it previously) | ALT+O, and then press F
Switch focus between the navigation pane and the topic pane | F6
Scroll through a topic | UP ARROW or DOWN ARROW
Move to the end or beginning of a topic | CTRL+END and CTRL+HOME
Scroll through the links in a topic, or through all of the options on a tab | TAB

The following keyboard shortcuts can be used for navigation on the Contents tab.

Action | Shortcut
Display the Contents tab | ALT+C
Open a book or folder | ENTER
Close a book or folder | ENTER
Select a topic | DOWN ARROW or UP ARROW
Display the selected topic | ENTER

The following keyboard shortcuts can be used for navigation on the Index tab.

Action | Shortcut
Display the Index tab | ALT+N
Select a keyword in the list | UP ARROW or DOWN ARROW
Display the associated topic | ALT+D

The following keyboard shortcuts can be used for navigation on the Search tab.

Action | Shortcut
Display the Search tab | ALT+S
Start a search | ALT+L
Select a topic in the results list | ALT+T, and then UP ARROW or DOWN ARROW
Display the selected topic | ALT+D


Using Help

You can access the add-in kit Help file, which includes all of the documentation for the OLAP Manager Add-in Kit 1, by pressing F1 or clicking Help in an add-in dialog box. You can also open the Help file, msmda01a.chm, directly from Microsoft® Windows NT® Explorer. By default, Setup installs this file to ..\Program Files\OLAP Services\Help.

OLAP Services Books Online (msdssbol.chm), which includes documentation for Microsoft SQL Server™ version 7.0 OLAP Services, is included with OLAP Services. By default, Setup installs this file to ..\Program Files\OLAP Services\Help. It is also available from the SQL Server 7.0 compact disc.

The OLAP Manager Add-in Kit 1 also includes a readme file, AddinsReadMe.txt, which contains important or late-breaking information that supplements this documentation. Setup automatically installs AddinsReadMe.txt to the directory where OLAP Services is installed. By default this directory is ..\Program Files\OLAP Services.


Getting Help from Microsoft Technical Support

If you have a simple question and need an answer fast

Quickly find answers yourself online

Use Support Online to search the Microsoft Knowledge Base and other technical resources for fast, accurate answers. You can also customize the site to control your search. To begin your search, go to http://support.microsoft.com/support.

If your question is urgent or more complex

Telephone Microsoft Technical Support

Work with a support engineer to solve your issue.

Pay-Per-Incident Support

Telephone support for Microsoft® SQL Server™ version 7.0 and its components is available on a Pay-Per-Incident basis. Support fees will be billed to your VISA, MasterCard, or American Express card. In the United States and Canada, for a fee of $195 US per incident, call (800) 936-5900, 24 hours a day, seven days a week, including holidays. Versions of Microsoft Internet Explorer that may have been included with this product have separate support telephone numbers, pricing, and hours of operation. See the technical support topic in Help in your Internet Explorer product for support information.

If your question isn’t urgent

Send e-mail to a Microsoft Technical Support Engineer

E-mail is ideal for questions requiring attached files: submit a question anytime and receive a response within one business day. Submit a question with Pay-Per-Incident Web Response at http://support.microsoft.com/support for a fee of $195 US.

For additional support needs

If you need text telephone (TTY/TDD)

Available Monday through Friday, excluding holidays. In the United States, call (425) 635-4948, from 6:00 A.M. to 6:00 P.M. Pacific time. In Canada, call (905) 568-9641, from 8:00 A.M. to 8:00 P.M. Eastern time.

If you don’t have access to the Internet

FastTips is a fax-back service providing Knowledge Base articles and answers to common questions. (800) 936-4400.

If you need support services for a business

Priority Annual, Priority Plus, and Premier provide a selection of support packages geared for businesses. To learn which account meets your needs, go to http://support.microsoft.com/support. Or call (800) 936-3500 for information on accounts for small- to medium-sized businesses and (800) 936-3200 for large, enterprise businesses.

If you need onsite, multivendor, or proprietary product support

Microsoft Certified Solution Providers (MCSPs) and Authorized Support Centers (ASCs) specialize in providing support packages for hardware, network, and software products from both Microsoft and other vendors. For more information about MCSPs, call (800) 765-7768 or visit http://www.microsoft.com/mcsp. For more information about ASCs, contact your Microsoft account representative, or visit http://www.microsoft.com/enterprise/asc.htm.

If you need support outside the United States and Canada

The services and prices listed here are available in the United States and Canada only. Support outside the United Sates and Canada may vary. Microsoft’s support services are subject to Microsoft’s then-current prices, terms, and conditions, which are subject to change without notice.

Microsoft Technical Support Worldwide

If you are outside the United States and have a question about a Microsoft product, first:

  • Check the information in this Help file or product manual.
  • Check the readme files that come with your product disks. These files provide general information that became available shortly before the product was released.
  • Go to Support Online from Microsoft Technical Support at http://support.microsoft.com/support.

If you cannot find a solution, you can receive information on how to obtain product support by contacting the Microsoft subsidiary office in your area.

Contacting a Microsoft Subsidiary

When you call your local Microsoft subsidiary, you should be at your computer and have the appropriate product documentation at hand. Be prepared to provide the following information:

  • The version of the Microsoft product you use.
  • The type of hardware you use, including network hardware, if applicable.
  • The operating system you use.
  • The exact wording of any messages that appeared on your screen.
  • A description of what happened and what you were doing when the problem occurred.
  • A description of how you tried to solve the problem.

Important Phone numbers for Microsoft Subsidiaries are subject to change. For the most recent listing, see the options and phone number section on the Support Online Web site at http://support.microsoft.com/support. If there is no Microsoft subsidiary office in your area, contact the establishment from which you obtained your Microsoft product.

Argentina
Microsoft de Argentina S.A.
Customer Service: (54) (1) 316-4600
Fax: (54) (1) 316-1922
Technical Support: (54) (1) 316-4664

Armenia
See Russia

Australia
Microsoft Pty. Ltd.
Phone: Products and Services 13 20 58
Fax: (61) (02) 9870-2285
Sales Information Centre: (61) (02) 9870-2100
Internet: http://www.microsoft.com.au/
Technical Support: (61) (02) 9870-2131
Fax: (61) (02) 9805 0519

Austria
Microsoft Ges.m.b.H.
Phone: (+43) 1 610 64 -0
Fax: (+43) 1 610 64 -200
Information: 0660-6520
Prices, updates, and so on: 0660-6520
Standard Support:
Installation and Handling: 01 50222-2255
General information about Support Network in Central Europe:
Fax: 01805-251191

Azerbaijan
See Russia

Belgium
Microsoft NV
Phone: +32-2-730 39 11
Fax: +32-2-726 96 09
Microsoft Support Network Info. Center: 0800 18307
TechNet Subscription: +353 1 703 8738
MSDN™ Subscription: +353 1 708 8690
Microsoft Information Center: +32-2-481 52 52
Bulletin Board: +32-2-726 85 45 (14400/1200/2400/9600 bd, 8N1, ANSI)
Technical Support:
+32-2-513 32 74 (Dutch speaking)
+32-2-502 34 32 (English speaking)
+32-2-513 22 68 (French speaking)
Internet: http://www.microsoft.com/benelux/support

Belorussia
See Russia

Bolivia
See Uruguay

Brazil
Microsoft Informatica Ltda.
Phone: (55) (11) 5514 -7100
Fax: (55) (11) 5514 - 7106/5514-7107
Technical Support: (55) (11) 5506-8087
Fax: (55) (11) 5506-7621
Automatic Fax: (55) (11) 5506-8506
Technical Support Bulletin Board Service: (55) (11) 5506-1234
Customer Support: (55) (11) 822-5764
Customer Services Fax: (55) (11) 822-6227

Canada
Microsoft Canada Co.
Head Office Phone: 1 (905) 568-0434
Customer Information Centre: 1 (800) 563-9048
Technical Support:
Desktop Systems including Microsoft Windows 95/98: (905) 568-4494
Microsoft Office and Office Components: (905) 568-2294
Other Standard Technical Support: (905) 568-3503
Priority Support Information: (800) 668-7975
Text Telephone (TTY/TDD) (905) 568-9641
Technical Support Bulletin Board Service: (905) 507-3022

Caribbean
Microsoft Caribbean, Inc.
Phone: (809) 273-3600
Fax: (809) 273-3636
Technical Support: (408) 953 8086
Support E-mail: mscarsup@microsoft.com
Customer Service: (800) 297 5982 for area codes 787 & 809 only
Customer Service E-mail: msccatus@microsoft.com

Central America
Technical Support: (506) 298 2020
Support E-mail: mscasup@microsoft.com
Customer Service: (506) 298 2000
Customer Service E-mail: msccatus@microsoft.com

Chile
Microsoft Chile S.A.
Phone: 56-2-330-6000
Fax: 56-2-330-6190
Customer Service: 56-2-800-330600
Personal Operating System and Desktop Applications:
Phone: 56-2-330-6222
Fax: 56-2-341-1439

People's Republic of China
Microsoft (China) Co. Ltd
19th Floor, Metro Tower
No. 30 Tian Yao Qiao Road,
Shanghai, 200030, P.R.C.
Phone: 011-86-21-64691166; 011-86-21-64691188
Fax: 011-86-21 64691188 Ext. 6988

Colombia
Microsoft Colombia
Phone: (571) 618 2245
Fax: (571) 618 2269
Technical Support:
Línea de Respuesta Microsoft
Phone: (571) 5230022
Fax: (571) 5231220
Voice Mail: (571) 5034216

Croatia
Microsoft Hrvatska d.o.o.
Phone: (+385) (1) 304 555
Fax: (+385) (1) 335 051
Technical Support: (Contact the subsidiary.)

Czech Republic
Microsoft s.r.o.
Phone (+42) (2) 611 97 111
Fax: (+42) (2) 611 97 100
Technical Support: (+420) (2) 2150 3222

Denmark
Microsoft Denmark
Phone: (45) 44 89 01 00
Fax: (45) 44 68 55 10
Technical Support:
Phone: (45) 44 89 01 11
Microsoft Sales Support: (45) 44 89 01 90
Microsoft FastTips: (45) 44 89 01 44
(Document 303030 in FastTips contains detailed instructions.)
Microsoft MSDL (BBS): (45) 44 66 90 46

Dominican Republic
See Central America

Dubai
Microsoft Middle East
Phone: (971) 4 513 888
Fax: (971) 4 527 444
Technical Support:
Personal Operating Systems and Desktop Applications only:
Phone: (971) (4) 524 488
Fax: (971)- 4 - 524 495
E-mail: mts@emirates.net.ae
Priority Support Information: (971) (4) 555 752
Customer Service Center:
(Version upgrade / TechNet and MSDN subscription)
Phone: (971) (4) 655 082
Fax: (971) (4) 655 097

Ecuador
Corporation Microsoft del Ecuador S.A.
Phone: (593) 2 460-447, (593) (2) 460-451
Customer Service: (593) (2) 460-453, (593) (2) 460-458
Technical Support: (593) (2) 463-094

Egypt
Microsoft Egypt
Phone: +202-594 2445
Fax: +202-594 2194
Technical Support:
Personal Operating Systems and Desktop Applications only:
Phone: (202) 261 3991 / 6
Fax: (202) 403 2718

England
See United Kingdom

Estonia
See Germany
Technical Support: (+372) 650 49 99

Finland
Microsoft OY
Phone: +358-(0)9-525 501
Fax: +358-(0)9-878 8770
Technical Support:
Phone: +358-(0)9-525 502 5026
Standard +358 (0) 9-525-502-500
Priority: +358 (0) 9-525-502-20
Premier: +358 (0) 9-525-502-03
Microsoft MSDL: +358-(0)9-878 77 99
(Information in Swedish and English): +358 (0) 9-455-03-66
Microsoft FastTips: +358-(0)9-525 502 550
(Information in Swedish and English)

France
Microsoft France
Phone: (33) 01 69-86-46-46
Fax: (33) 01 64-46-06-60
Telex: MSPARIS 604322
Technical Support:
Phone: (33) 01-69-86-10-20
Fax: (33) (01) 69-28-00-28

French Polynesia
See France

Georgia
See Russia

Germany
Microsoft GmbH
Phone: 089/3176-0
Fax: 089/3176-1000
Telex: (17) 89/83 28 MS GMBH D
Information: 089/3176 1199
Prices, updates, and so on: 08105-25-1199
Internet: http://www.microsoft.com/germany
Standard Support:
Installation and Handling: 01805/67 22 55
General information about Microsoft support in Central Europe:
Fax: 1805-25-1191

Greece
Microsoft Hellas, S.A.
Phone: (30)(1) 6806-775 through (30)(1) 6806-779
Fax: (30)(1) 6806-780
Technical Support:
Phone: (30) (1) 9247-030
Fax: (30) (1) 9215-363

Hong Kong SAR, PRC
Microsoft Hong Kong SAR, PRC Limited
Fax: (852) 2560-2217
Product Support Faxback Service: (852) 2535-9293
Microsoft Club Hotline: (852) 28044277
Technical Support: (852) 2804-4222

Hungary
Microsoft Hungary
Microsoft Magyarország Kft.
Phone: +36 (1) 327-2800
Fax: +36 (1) 327-2899
Technical Support: +36 (1) 267-4636 (2MSINFO)

Iceland
See Denmark

Ireland
See United Kingdom

India
Microsoft India
Phone: (011) (91) (80) 559 5733, (011) (91) (11) 646 0767, 646 0694
Fax: (011) (91) (80) 559-7133, (011) (91) (11) 647-4714

Indonesia (SP)
Microsoft Indonesia - Jakarta
Technical Support:
Phone: 62 21 570 42 54
Fax: 62 21 520 81 22

Israel
Microsoft Israel Ltd.
Phone: 972-3-613-0833
Fax: 972-3-613-0834

Italy
Microsoft SpA
Phone: (39) (2) 7039-21
Fax: (39) (2) 7039-2020
Microsoft Rome Office: (39) (6) 5095-01 - Fax: (39) (6) 5095-0600
Microsoft by Fax (Fax-on-demand service): (39) (2) 70-398-888
Customer Service (New product info, product literature): (39) (2) 70-398-398
Dealer Support (Customer Service for resellers only): (39) (2) 70-398-388
Technical Support: (39) (2) 70-398-398
Microsoft Consulting Service: (39) (2) 7039-21

Japan
Microsoft Company Ltd.
Technical Support (Standard Support): (81) (424) 41-8700
(Technical Support Options/ Support Contract)
Phone: 0120-37-0196 (toll-free domestic only)
Channel Marketing Information Center:
(Presales Product Support)
Tokyo Phone: (81) (3) 5454-2300
Osaka Phone: (81) (6) 245-6995
Customer Service Phone:
(Version upgrade/Registration)
Phone: (81) (48) 226-5500
Fax: (81) (48) 226-5511

Kazakhstan
See Russia

Kirgizia
See Russia

Korea
Microsoft CH
Phone: (82) (2) 531-4500
Fax: (82) (2) 531-1724
Customer Service: 080-022-7337 (toll-free domestic only)
Technical Support:
Office Technical Support: (82) (2) 508-0040
Windows Technical Support: (82) (2) 563-0054
Developer Technical Support: (82) (2) 566-0071
Microsoft FastTips: (82) (2) 3453-7555
Support Sales: (82) (2) 531-4544

Latin America
Microsoft
Latin American Headquarters (U.S.A.)
Phone: (305) 489-4800
Fax: (305) 491-1616
Customer Service: (425) 936-8661
Technical Support: (214) 714-9100
Internet: http://www.microsoft.com/latam/soporte

Liechtenstein
See Switzerland (German speaking)

Luxembourg
Microsoft NV
Phone: +32-2-730 39 11
Microsoft Support Network Info. Center: +31-800-9977-57
TechNet Subscription: +353-1-703 8738
MSDN Subscription: +353-1-708-8690
Microsoft Information Center: +32-2-481 52 52
Bulletin Board: +32-2-726 85 45
(1200/2400/9600/14400 bd, 8N1, ANSI)
Technical Support:
+32-2-513 32 74 (Dutch speaking)
+32-2-502 34 32 (English speaking)
+32-2-513 22 68 (French speaking)
Internet: http://www.microsoft.com/benelux/support

Malaysia (SP)
Microsoft (Malaysia) Sdn Bhd:
Phone: (60-3) 793-9595
Fax: (60-3) 791-6080

México
Microsoft México, S.A. de C.V.
267-2110 Atención a Distribuidores
265-3380 Atención a Clientes
265-3399 Soporte Sistemas Operativos y Applicaciones de Escritorio
267-2190 Soporte de Servidores y Herramientas de Desarrollo
Fast Tips: (52)(5) 2612199
Microsoft BBS 628-6200/02 MSMEXICO sin password

Morocco
Microsoft Afrique Du Nord
Phone: (212) 2 47 10 72
Fax: (212) 2 47 10 86

Netherlands
Microsoft BV
Phone: 023-5689189
Customer Service: 023-5677700
Microsoft Support Network Info Centre: 0800 099 7757
TechNet Subscription: 0800 022 6801
MSDN Subscription: 0800 022 7261
Bulletin Board: 023-5634221
(1200/2400/9600/14400bd, 8N1, ANSI)
Technical Support:
023-5677877 (Dutch speaking)
023-5677853 (English speaking)
Internet: http://www.microsoft.com/benelux/support

New Zealand
Microsoft New Zealand Ltd
Phone: 64 (9) 357-5800
Fax: 64 (9) 358-3726
Technical Support:
Phone: 64 (9) 357-5575
Fax: 64 (9) 307-0516 and 357-5577

Northern Ireland
See United Kingdom

Norway
Microsoft Norway AS
Phone: +(47) 22 02 25 00
Fax: +(47) 22 95 06 64
Technical Support: Standard: +(47) 22 02 25 50
Priority: + (47) 22 02 25 45
Premier: + (47) 22 02 25 46
Microsoft Sales Support: +(47) 22 02 25 80
Microsoft MSDL: +(47) 22 18 22 09
Microsoft FastTips: +(47) 22 02 25 70
(Document 404040 in FastTips contains detailed instructions)

Panama
Technical Support:(506) 298 2020
Support E-mail: mscasup@microsoft.com
Customer Service: (506) 298 2000
Customer Service E-mail: msccatus@microsoft.com

Papua New Guinea
See Australia

Paraguay
See Uruguay

Peru
Centro de Servicio al Cliente
Central de Soporte: (51) (1) 422- 4116
Fax: (51) (1) 221 9052
Central de Información (51) (1) 242-5980
Fax: (51) (1) 447 8591

Philippines (SP)
Microsoft Philippines
Phone: 632 811 0062
Technical Support:
Phone: 632 892 2295/2495 and 632 813-2494
Fax: 632 813 2493

Poland
Microsoft Sp. z o.o.
Phone: +(48-22) 661-54-00
Fax: +(48-22) 661-54-34
Information Service: +(48-22) 865-99-33
Technical Support: +(48-22) 865-99-66

Portugal
Microsoft Portugal MSFT, Lda.
Phone: (351) 1 4409200
Fax: (351) 1 4412101
Technical Support:
Standard Support: (351) 1 4409280/1/2/3
Fax: (351) 1 4411655

Puerto Rico
Technical Support:(408) 953 8086
Support E-mail: mscarsup@microsoft.com
Customer Service: (800)297 5982 for area codes 787 and 809 only
Customer Service E-mail: msccatus@microsoft.com

Republic of Ireland
See United Kingdom

Romania
Microsoft s.r.l.
Phone: (+40) (1) 222 90 16
Fax: (+40) (1) 222 90 12
Technical Support: (+40) (1) 312 09 48

Russia
Microsoft Z.A.O.
Microsoft Information Center: (095) 916 7171
Russia: 125252
Moscow: Postbox 70
Technical Support:
Moscow (095) 745-54-45
St.Petersburg: (812) 118-36-36
Novosibirsk (3832) 119-019
Internet: http://www.microsoft.com/rus/

Scotland
See United Kingdom

Saudi Arabia
Microsoft Saudi Arabia
Phone: +966-1-488-1165
Fax: +966-1-488-1576
Technical Support:
(Personal Operating System and Desktop Applications only):
Phone: 800 124 0500 (toll-free within Saudi Arabia)
Fax: 966-1-4740576

Singapore
Microsoft Singapore Pte Ltd
Phone: (65) 337-6088
Fax: (65) 337-6788
Customer Services:
Phone: (65) 324-5255
Fax: (65) 324-6181
Product Support Services:
Phone: (65) 378-3600
Fax: (65) 378-3662

Slovenia/Slovenija
Microsoft d.o.o.
Phone: (+386) (61) 1800 8-00
Fax: (+386) (61) 1800 822
Technical Support:(+386) (61) 185 3449

Slovak Republic
Microsoft Slovakia s.r.o.
Phone (+421) (7) 37 63 02
Fax: (+421) (7) 37 66 71
Technical Support: (+421) (7) 531 20 83

South Africa
Microsoft South Africa
Phone: (27) 11 445 0000
Fax: (27) 11 445 0046
Technical Support:
(Toll Free): 0 802 11 11 04
(Toll): (27) 11 445 0000
Fax: (+27) 11 445-0343

South East Africa and Indian Ocean Islands
See South Africa

Spain
Microsoft Iberica SRL
Phone: (34) 1-807-9999
Fax: (34) 1-803-8310
Technical Support: (34) 1-3754004
Customer Service: (34) 1-3754004
From Spain Only: 902-187 198
Fax Back Telephone: (34) 1-3754004

Sweden
Microsoft AB
Phone: +46-(0)8-752 56 00
Fax: +46-(0)8-750 51 58
Technical Support:
Standard: +46-(0) 8- 752 09 29
Priority: +46 (0) 8 751 09 80
Premier: +46 (0) 8 632 57 01
Sales Support: +46-(0)8- 752 56 30
Microsoft MSDL: +46-(0)8-750 47 42
Microsoft FastTips: +46-(0)8-752 29 00
(Document 202020 in FastTips contains detailed instructions.)

Switzerland
Microsoft AG
Phone: 01-839 61 11
Fax: 01-831 08 69
Prices, updates, and so on: 01/839 61 11
Documentation:
Phone: 155 59 00
Fax: 064-224294, Microsoft Info-Service, Postfach, 8001 Zürich
Standard Technical Support: Installation and Handling:
0848 80 - 2255
Technical Support (French speaking): 022-738 96 88
General information about Microsoft Support in Central Europe:
Tel. 0848-80-2330
Internet: http://www.microsoft.com/switzerland

Tadzhikistan
See Russia

Taiwan Region
Microsoft Taiwan Region Corp.
Phone: (886) (2) 504-3122
Fax: (886) (2) 504-3121
Technical Support: (886) (2) 508-9501

Thailand
Microsoft (Thailand) Limited
Phone: (662) 266-3300
Fax: (662) 266-3310
Product Support:
Phone: (662) 613-7208 through 11
Fax: (662) 613-7198

Turkey
Microsoft Turkey
Phone: 90 (212) 258 59 98
Fax: 90 (212) 258 59 54
Support Hotline:
Phone: 90 (212) 258 96 66
Fax: 90 (212) 258 95 99
Bulletin Board Service: 90 (212) 227 93 90

Turkmenistan
See Russia

United Kingdom
Microsoft Limited
Fax: (0870) 60 20 100
Phone: (0870) 60 10 100
Microsoft KeyData:
(Bulletin Board Service)
(0870) 50 30 200 (up to 28.8Kbaud, n, 8, 1)
Microsoft KeyFax:
(Faxback Information Service)
(0870) 50 30 100
Technical Support:
Desktop Applications, Home Products, Desktop Systems and hardware:
0870 50 10 100
Developer Products: 0870 50 10 200
Business Systems: 0870 50 10 300
Customers phoning from Ireland should dial 706 5353 for technical support.
Minicom Line 0870 50 30 400
Pre-Sales Information: Microsoft Connection (0345) 00 2000

Uruguay
Technical Support: (598) (2) 774934
Fax: (598) (2) 774935

Uzbekistan
See Russia

Venezuela
Corporation MS 90 de Venezuela S.A.
Other information: (582) 265-9922
Fax: (582) 265-0863
Technical Support: (582) 993-6755

Wales
See United Kingdom


Finding Information on the Internet

Information about Microsoft® SQL Server™ is available on the Internet.

Description | Address
General information about SQL Server | http://www.microsoft.com/sql
General information about OLAP Services | http://www.microsoft.com/sql/olap
General information about English Query | http://www.microsoft.com/sql/eq
Technical support | http://www.microsoft.com/support
Microsoft Usenet newsgroups, where you can interact with other SQL Server users | news://msnews.microsoft.com
Microsoft newsgroup where you can interact with other OLAP Services users | microsoft.public.sqlserver.olap


Accessibility for People with Disabilities

Microsoft is committed to making its products and services easier for everyone to use. This section provides information on the following features, products, and services that make Microsoft® Windows®, Microsoft Windows NT®, and Microsoft SQL Server™ more accessible for people with disabilities:

  • Accessibility of Microsoft SQL Server 7.0
  • Features and hints for customizing Windows or Windows NT
  • Microsoft services for people who are deaf or hard-of-hearing
  • Microsoft software documentation online, or on audiocassette, floppy disk, or compact disc
  • Third-party utilities that enhance accessibility
  • Other products and services for people with disabilities

Note The information in this section applies only if you acquired this Microsoft product in the United States. If you obtained this product outside the United States, your package contains a subsidiary information card listing Microsoft support services telephone numbers and addresses. You can contact your subsidiary to find out whether the type of products and services described here are available in your area.

Customizing Windows or Windows NT

There are many ways you can customize Windows or Windows NT to make your computer more accessible.

  • Accessibility features have been built into Windows and Windows NT since the introduction of Windows 95. These features are useful for individuals who have difficulty typing or using a mouse, have moderately impaired vision, or who are deaf or hard-of-hearing. The features can be installed during setup, or you can add them later from your Windows installation disks.

    For more information about installing and using these features, see Accessibility in the Windows Help Index.

  • Some of the accessibility features built into Windows and Windows NT can be added to earlier versions of those products, and to MS-DOS®, through Access Packs. You can download these files or you can order them on disks from Microsoft. (See Accessibility Notes and Utilities to Download later in this section.)
  • You also can use Control Panel and other built-in features to adjust the appearance and behavior of Windows or Windows NT to suit varying vision and motor abilities. These include adjusting colors and sizes, sound volume, and the behavior of the mouse and keyboard.
  • In Windows 98, the majority of accessibility settings can be set through the Accessibility Wizard or Control Panel. The Accessibility Wizard presents features sorted by disability, making it easy to customize Windows to each individual’s needs. The Accessibility Wizard also enables you to save your settings to a file that can be used on another computer.
  • Dvorak keyboard layouts make the most frequently typed characters on a keyboard more accessible if you have difficulty using the standard QWERTY layout. There are three Dvorak layouts: one if you are a two-handed user, one if you type with your left hand only, and one if you type with your right hand only. You do not need to purchase any special equipment to use these features.

The specific features available, and whether they are built-in or added separately, depend on which operating system you are using.

For full documentation on the accessibility features available in the operating system you are using, obtain the documents listed below. Accessibility features are also documented in the Microsoft Windows 95 Resource Kit, the Microsoft Windows 98 Resource Kit and the Microsoft Windows NT Resource Kit.

Accessibility Notes and Utilities to Download

These documents explain how to customize Microsoft Windows and Windows NT for users with disabilities. Specific instructions for downloading the files immediately follow this list.

For | You need
Customizing Windows for Individuals with Disabilities (describes all of the other documents in this list and includes links to download them; this article will be updated when new versions of Microsoft operating systems are released) | Microsoft Knowledge Base article Q165486.
Customizing Microsoft Windows 98 for individuals with disabilities | Cst_W98.exe
Customizing Microsoft Windows 95 for individuals with disabilities | Cst_W95.exe
Customizing Microsoft Windows NT 4.0 for individuals with disabilities | Cst_NT4.exe
Customizing Microsoft Windows NT 3.1 and 3.5 for individuals with disabilities (includes Access Pack for Microsoft Windows NT, which provides features for people who have difficulty using a keyboard or mouse, or who are deaf or hard-of-hearing) | Cst_NT3x.exe
Customizing Microsoft Windows 3.1 for individuals with disabilities | Cst_W3x.exe
Customizing Microsoft Windows for Workgroups 3.1 for individuals with disabilities | Cst_WG3x.exe
Customizing Microsoft Windows 3.0 for individuals with disabilities | Cst_W30.exe
Access Pack for Microsoft Windows 3.0 and 3.1, which provides features for people who have difficulty using a keyboard or mouse, or who are deaf or hard-of-hearing | ACCP.EXE
Dvorak keyboard layouts for people who type with one hand (already included in Windows NT version 3.5 and later) | GA0650.EXE

To Download the Files

If you have a modem or other type of network connection, you can download these accessibility files from the following network services:

  • The Microsoft Accessibility and Disabilities Web site on the Internet, http://microsoft.com/enable.
  • Support Online from Microsoft Technical Support at http://support.microsoft.com/support. You can choose an application from the list labeled “My search is about,” and then search on a specific application. You can also choose “All Products” and enter “Q165486” in the text box labeled “My question is.” The search results will display a link to the Knowledge Base article, “Customizing Windows for Individuals with Disabilities,” which includes links to all of the documents listed above.

    For other accessibility articles, choose the appropriate application from the list labeled “My search is about” and enter “kbenable” in the text box labeled “My question is.”

  • Microsoft Internet server at ftp.microsoft.com, in /softlib/mslfiles.
  • Microsoft Download Service (MSDL), which you can reach by calling (425) 936-6735 any time except between 1:00 A.M. and 2:30 A.M. Pacific time.

    MSDL supports 1200, 2400, 9600, or 14400 baud rates (V.22bis, V.32, V.32bis and V.42), with 8 data bits, no parity, and 1 stop bit. MSDL does not support 28800, 56K or ISDN connections.

Microsoft Services for People Who Are Deaf or Hard-of-Hearing

If you are deaf or hard-of-hearing, complete access to Microsoft product and customer services is available through a text telephone (TTY/TDD) service.

Customer Service

You can contact the Microsoft Sales Information Center on a text telephone by dialing (800) 892-5234 between 6:30 A.M. and 5:30 P.M. Pacific time.

Technical Assistance

For technical assistance in the United States, you can contact Microsoft Technical Support on a text telephone at (425) 635-4948 between 6:00 A.M. and 6:00 P.M. Pacific time, Monday through Friday, excluding holidays. In Canada, dial (905) 568-9641 between 8:00 A.M. and 8:00 P.M. eastern time, Monday through Friday, excluding holidays. Microsoft support services are subject to the prices, terms, and conditions in place at the time the service is used.

Microsoft Documentation in Alternative Formats

In addition to the standard forms of documentation, many Microsoft products are available in other formats to make them more accessible.

Microsoft SQL Server 7.0 provides documentation in the form of Books Online (SQLBOL.chm, MSDSSBOL.chm) and online Help, which are installed automatically with the product. You can increase the font size of topics to make them more readable and print them as needed. Keyboard shortcuts are included in some of the Help systems for SQL Server applications as well.

If you have difficulty reading or handling printed documentation, you can obtain many Microsoft publications from Recording for the Blind & Dyslexic, Inc. RFB&D distributes these documents to registered, eligible members of their distribution service, either on audio cassettes or on floppy disks. The RFB&D collection contains more than 80,000 titles, including Microsoft product documentation and books from Microsoft Press®. You can download many of these books from the Microsoft Web site at http://microsoft.com/enable/.

For more information, contact Recording for the Blind & Dyslexic at the following address or phone numbers:

Recording for the Blind & Dyslexic, Inc.
20 Roszel Road
Princeton, NJ 08540 		Phone:
Fax:
World Wide Web: 		(609) 452-0606
(609) 987-8116
http://www.rfbd.org/

Utilities to Enhance Accessibility

A wide variety of hardware and software products are available to make personal computers easier to use for people with disabilities. Among the different types of products available for the MS-DOS, Windows, and Windows NT operating systems are:

  • Programs that enlarge or alter the color of information on the screen for people with visual impairments.
  • Programs that describe information on the screen in Braille or synthesized speech for people who are blind or have difficulty reading.
  • Hardware and software utilities that modify the behavior of the mouse and keyboard.
  • Programs that enable people to “type” by using a mouse or their voice.
  • Word or phrase prediction software that allow users to type more quickly and with fewer keystrokes.
  • Alternative input devices, such as single switch or puff-and-sip devices, for people who cannot use a mouse or a keyboard.

Getting More Accessibility Information

In addition to the features and resources already described in this section, other products, services, and resources for people with disabilities are available from Microsoft and other organizations.

Microsoft

Microsoft provides a catalog of accessibility aids that can be used with the Windows and Windows NT operating systems. You can obtain this catalog from our Web site or by phone:

Microsoft Sales Information Center
One Microsoft Way
Redmond, WA 98052-6393 		World Wide Web:
Voice telephone:
Text telephone: 		http://microsoft.com/enable/
(800) 426-9400
(800) 892-5234

Macintosh Disability Solutions

For more information on products and services for the Macintosh for people with disabilities, contact:

Apple Worldwide Disability Solutions Group World Wide Web:
Voice telephone:
Text telephone: 		http://www.apple.com/disability/
(800) 600-7808
(800) 755-0601

Trace R&D Center

The Trace R&D Center at the University of Wisconsin-Madison publishes a database of more than 18,000 products and other information for people with disabilities. The database is available on their site on the World Wide Web. The Trace R&D Center also publishes a book, titled Trace ResourceBook, which provides descriptions and photographs of about 2,000 products.

To obtain these materials, contact:

Trace R&D Center
University of Wisconsin
S-151 Waisman Center
1500 Highland Avenue
Madison, WI 53705-2280 		World Wide Web:
Fax:
 http://trace.wisc.edu/
(608) 262-8848

Assistive Technology Programs and Trained Evaluators

Computers and other assistive devices can help persons with disabilities to overcome a variety of barriers. For general information and recommendations on how computers can help you with your specific needs, you should consult a trained evaluator. For information about locating programs or services in your area that may be able to help you, please contact:

National Information System
University of South Carolina
Columbia, SC 29208 		Voice/text telephone:
Fax: 		(803) 777-1782
(803) 777-9557


Introducing Add-ins

Add-ins are programs that interact with and enhance the OLAP Manager user interface. Add-ins can be created by Microsoft, other vendors, or you. For information about creating your own add-ins, see “Add-in Programs” and its subtopics in OLAP Services Books Online (msdssbol.chm).

One add-in, the Migrate Repository wizard, is included with Microsoft® SQL Server™ version 7.0 OLAP Services. Other add-ins are installed separately.

To view a list of add-ins installed on a server computer

  1. In the OLAP Manager tree view, right-click the server, and then click Properties.
  2. Click the Add-ins tab.

In the Add-ins tab, checked add-ins are enabled; unchecked add-ins are disabled.

Each add-in may have a different installation procedure. The installation procedure for the OLAP Manager Add-in Kit 1 is described in Installing the Add-ins. After installation, you are ready to begin using the add-ins.


Installing the Add-ins

This topic and its subtopics describe installation of the OLAP Manager Add-in Kit 1.

This add-in kit must be installed separately on each server computer on which you want to use the add-ins. The OLAP Manager must be installed on each server computer prior to installation of the add-in kit.

The minimum system requirements for this add-in kit are the same as for Microsoft® SQL Server™ version 7.0 OLAP Services. An exception is that the add-in kit is not supported on DEC Alpha and compatible systems. For more information, see “System Requirements” in OLAP Services Books Online (msdssbol.chm).


Downloading the Add-ins

You can download the OLAP Manager Add-in Kit 1 from the Internet site for Microsoft® SQL Server™ OLAP Services. To access this site, start the OLAP Manager, and click OLAP Services on the Web on the Getting Started tab of the HTML (that is, right) pane.


Running Setup

To start the Setup program for the OLAP Manager Add-in Kit 1, run addins.exe on the computer where the OLAP Manager is installed. The Setup program takes you through the steps required to install the add-in kit. Setup allows you to select which add-ins you want to install. If the OLAP Manager is running while you install the add-ins, you must exit and then start the OLAP Manager before you can access the add-ins.

The add-in kit includes a readme file, AddinsReadMe.txt. Refer to this file for important or late-breaking information that supplements this document. Setup automatically installs AddinsReadMe.txt to the directory where Microsoft® SQL Server™ OLAP Services is installed. By default this directory is ..\Program Files\OLAP Services.


Disabling and Enabling Add-ins

The add-ins in the OLAP Manager Add-in Kit 1 are enabled automatically upon installation.

After installation you can disable and enable individual add-ins in the Add-ins tab of the Properties dialog box.

To disable or enable an add-in

  1. In the OLAP Manager tree view, right-click a server, and then click Properties.
  2. Click the Add-ins tab.
  3. To disable an add-in, clear the check box beside its name. To enable an add-in, select the check box beside its name. Click OK.
  4. Exit the OLAP Manager.
  5. Start the OLAP Manager.


Using the Add-ins

This topic and its subtopics describe how to use the OLAP Manager Add-in Kit 1, including:

  • Archive and Restore Databases Add-in

    Enables you to use the OLAP Manager to archive and restore Microsoft® SQL Server™ OLAP Services databases. You can also can archive and restore by using the msmdarch command within the Command Prompt window or a .bat file.

  • Calculated Member Manager Add-in

    Enables you to use the OLAP Manager to create and maintain calculated members in virtual cubes.

  • Copy and Paste Objects Add-in

    Enables you to copy and paste objects in the OLAP Manager tree view.

These add-ins provide new options on the following menus:

  • The shortcut (that is, right-click) menus in the OLAP Manager tree view
  • The OLAP Manager Action menu

You can access each of the three add-ins through the OLAP Manager user interface:

  • To access the Archive and Restore Databases Add-in, in the OLAP Manager tree view, right-click a database, and then click Archive.
  • To access the Calculated Member Manager Add-in, in the OLAP Manager tree view, right-click a virtual cube, and then click Manage Calculated Members.
  • To access the Copy and Paste Objects Add-in, in the OLAP Manager tree view, right-click the object you want to copy, and then click Copy. No dialog box is displayed, but the object is copied and can then be pasted. Copy is not available for all object types. For more information, see Copy and Paste Objects Add-in.


Archive and Restore Databases Add-in

The Archive and Restore Databases Add-in enables you to use the OLAP Manager or msmdarch command to archive and restore Microsoft® SQL Server™ OLAP Services databases. Each database is archived to a single .cab file, which you can reserve for restoration requirements or migrate to other server computers. This file is called an “archive file.”

A database’s archive file stores the contents of the Microsoft Windows NT® directory named the same as the database. For example, the archive file for the sample FoodMart database stores the contents of the FoodMart directory. The default path of this directory is:

..\Program Files\OLAP Services\Data\FoodMart\

All OLAP Services databases reside in the Data directory. The path leading to the Data directory can be changed. (To determine the current path, in the OLAP Manager, right-click the server that contains the database, click Properties, and then see the Data folder box.)

The archive file also stores metadata for the database and its objects. The appropriate records from the OLAP Services repository are included in the archive file. By default the OLAP Services repository is:

..\Program Files\OLAP Services\Bin\msmdrep.mdb

However, the path of the repository can be changed at installation, and the repository can be migrated to a SQL Server database.

Caution When you restore a database, its file set (in the Data directory and its subdirectories) and its metadata are returned to their states at the time the archive file was created. Files that were created since then in these directories are deleted. Changes and additions since then to OLAP Services repository records for the database and its objects are removed.

When you restore a database:

  • The files in the selected archive file are restored to the appropriate directories in the Data directory. If these directories already contain files with the same names as files in the archive file, the existing files are overwritten by the files in the archive file. If these directories contain files with different names, including new files that were created since the archive file was created, these files are deleted.
  • The records in the OLAP Services repository that are associated with the database and its objects are replaced by the OLAP Services repository records in the selected archive file.

If you restore a database containing ROLAP (relational OLAP) partitions and the restoration copies rather than replaces the original, archived partitions, the original ROLAP partitions and their copies will use the same aggregation tables. This situation may cause overwrite conflicts in the tables. For example, if you change and process an original partition, the aggregation tables may no longer be valid for its copy. To avoid this problem, immediately after restoration specify a unique aggregation prefix for each of the ROLAP partitions, and process the partitions whose aggregation prefixes you changed. This action creates different aggregation tables for each partition. To access the aggregation prefix for a partition, in the OLAP Manager tree view, right-click the partition, click Edit, advance to the final step of the Partition wizard, and then click Advanced.

Caution This add-in does not archive write-back tables, source data, or aggregations for ROLAP cubes and partitions. This data is required for ongoing, correct operation of OLAP Services. You must archive or back up this data with the backup software you ordinarily use because it is not contained in archive files created with this add-in.

Write-back tables are not stored in the archive file. Therefore, if you restore a write-enabled cube and its write-back table is not available, the cube must be processed before it can be used. After processing, the effects of the write-back data are absent from the cube’s data. For more information about write-back data and write-enabled cubes, see “Write-Enabled Cubes” in OLAP Services Books Online (msdssbol.chm).

The contents of the archive file vary according to the storage types of the cubes and partitions in the database. The following table indicates these variations.

Storage Type | Source Data Is Contained in Archive File | Version of Source Data Usable by Cube/Partition Is Contained in Archive File | Aggregations Are Contained in Archive File
MOLAP | No | Yes | Yes
ROLAP | No | No | No
HOLAP | No | No | Yes

Restored MOLAP (multidimensional OLAP) cubes are usable even if their source data (that is, the tables in the data source used by the cube) is lost or unavailable. However, a restored MOLAP cube cannot be updated if its source data is lost permanently. Restored ROLAP and HOLAP (hybrid OLAP) cubes rely on the availability of their source data for correct operation. Restored ROLAP cubes also rely on the availability of their aggregation tables, which are stored with the source data.


Archiving an OLAP Services Database

You can archive a Microsoft® SQL Server™ OLAP Services database by using the OLAP Manager or the msmdarch command.

To archive an OLAP Services database using the OLAP Manager, use the Archive Database and Archive Database Progress dialog boxes.

To archive an OLAP Services database with the OLAP Manager

  1. In the OLAP Manager tree view, right-click a database, and then click Archive Database.
  2. In the Archive Database dialog box, specify a file name and path for the archive file. If you are archiving a database on a remote server, you must also specify the path of the Data directory containing the files for the database. Click Archive.
  3. In the Archive Database Progress dialog box, monitor the progress of the archive process. You can cancel the archive process after it has begun. You can also save the archive log.

To archive an OLAP Services database using the msmdarch command, you can use the Command Prompt window.

To archive an OLAP Services database with the msmdarch command

  1. On your desktop click Start, point to Programs, and then click Command Prompt.
  2. (Optional.) Use the cd command to access the directory containing the msmdarch.exe file. By default, this file is at the following location:

    ..\Program Files\OLAP Services\Bin\

  3. Type a valid msmdarch command with the /a switch. The full syntax and an example are shown below.
  4. Press ENTER.

You can also execute the msmdarch command from a .bat file. If you want to schedule execution of a .bat file, it is recommended that you use Data Transformation Services.

Syntax

["command-path]msmdarch["] /a server "data-path" "database-name" "archive-path-and-file-name" ["log-path-and-file-name"]

command-path
The path containing the msmdarch.exe file. By default, this path is:

\Program Files\OLAP Services\Bin\

server
The server computer name that contains the database you want to archive.
data-path
The path of the Data directory that contains the files for the database you want to archive. By default, this path is:

\Program Files\OLAP Services\Data\

database-name
The name of the database you want to archive.
archive-path-and-file-name
The path, file name, and .cab extension of the archive file.
log-path-and-file-name
The path, file name, and .log extension of the archive log. If you specify an archive log that already exists, the new archive log is appended to it. If you specify an invalid path or file name, the archive log is written to the \Bin\DBArchive.log file, which by default is ..\Program Files\OLAP Services\Bin\DBArchive.log.
Example

The following command archives the sample FoodMart database included in OLAP Services.

"\Program Files\OLAP Services\Bin\msmdarch" /a myserver "\Program Files\OLAP Services\Data\" "FoodMart" "\My archives\server myserver\FoodMart.cab"


Archive Database Dialog Box

This dialog box enables you to:

  • Specify options for archiving a Microsoft® SQL Server™ OLAP Services database.
  • Begin the archive process, which produces an archive file.

This dialog box is displayed when in the OLAP Manager tree view you right-click a database and click Archive Database.

The archive file includes files for the selected database that are in a directory named the same as the database. This directory is in the Data directory, which contains a directory for each database on the server. For example, if you archive the FoodMart database, the files in the ...\Data\FoodMart\ directory are included in the archive file. For more information about the Data directory, see “Remote Path” later in this topic.

The archive file also stores metadata for the database and its objects. The appropriate records from the OLAP Services repository are included in the archive file. By default the OLAP Services repository is msmdrep.mdb, but it can be migrated to a SQL Server database.

For more information about archiving OLAP Services databases, see Archive and Restore Databases Add-in.

Options

File name

Specify a file name and extension for the archive file. The extension must be .cab.

Save in

Specify the path of the archive file. To browse available paths, click the ... button.

Remote Path

Specify the path of the Data directory that contains the files for the database you are archiving. To browse available paths, click the ... button.

The Data directory is created during installation of Microsoft® SQL Server™ OLAP Services and contains a directory for each database on the OLAP server. The default path of the Data directory is:

..\Program Files\OLAP Services\Data\

The \Program Files\OLAP Services\ portion of the path can be changed. (To determine the current value, right-click the remote server, click Properties, and then see the Data folder box.)

In the Remote Path box, you must precede the path of the Data directory with the remote server name. If the Data directory is shared at its disk level, you must also include the share name associated with the disk. The following example Remote Path value includes the server name Server-1 and the disk share name C$.

\\Server-1\C$\Program Files\OLAP Services\Data\

The Remote Path box appears only if the database you are archiving is on a remote server.

Archive

Begins the archive process. The Archive Database Progress dialog box is displayed in which you can monitor or cancel the archive process.

Cancel

Cancels the archive process.

Help

Displays this Help topic.


Archive Database Progress Dialog Box

This dialog box enables you to monitor the archiving of a Microsoft® SQL Server™ OLAP Services database. You can also cancel the archive process or save the archive log.

This dialog box is displayed when in the Archive Database dialog box you click Archive.

For more information about archiving OLAP Services databases, see Archive and Restore Databases Add-in.

Options

Save Log

Displays a dialog box in which you specify the path and file name of the archive log and then save it. If the archive process completes successfully, the archive log contains a list of the files contained in the archive file. Otherwise, the archive log contains one or more messages. The Save Log button is available only after the archive process is completed or canceled.

Cancel

Cancels the archive process. If you click Cancel and receive the “Archive canceled” message, nothing has been archived. A delay may occur between clicking Cancel and display of the message.

The Cancel button is replaced by the Close button after the archive process is completed or canceled.

Close

Closes the dialog box. The Close button is available only after the archive process is completed or canceled.

Help

Displays this Help topic.


Restoring an OLAP Services Database

You can restore a Microsoft® SQL Server™ OLAP Services database by using the OLAP Manager or the msmdarch command.

To restore an OLAP Services database with the OLAP Manager, use the Open Archive File, Restore Database, and Restore Database Progress dialog boxes. If you are restoring a database on a remote server, you must also use the Remote Server Data Directory dialog box.

To restore an OLAP Services database with the OLAP Manager

  1. In the OLAP Manager tree view, right-click a server, and then click Restore Database.
  2. If you are restoring a database on a remote server, the Remote Server Data Directory dialog box appears. Specify the path of the Data directory to which the database will be restored. This directory will contain the files for the database after the restoration is complete. Click OK.
  3. In the Open Archive File dialog box, specify the path and file name of the archive file, and then click Open.
  4. In the Restore Database dialog box, click Restore.
  5. In the Restore Database Progress dialog box, monitor the progress of the restoration. You can cancel the restoration after it has begun. You can also save the restore log.

To restore an OLAP Services database with the msmdarch command, you can use the Command Prompt window.

To restore an OLAP Services database using the msmdarch command

  1. On your desktop click Start, point to Programs, and then click Command Prompt.
  2. (Optional.) Use the cd command to access the directory containing the msmdarch.exe file. By default, this file is at the following location:

    ..\Program Files\OLAP Services\Bin\

  3. Type a valid msmdarch command with the /r switch. The full syntax and an example are shown below.
  4. Press ENTER.

You can also execute the msmdarch command from a .bat file. If you want to schedule execution of a .bat file, it is recommended that you use Data Transformation Services.

Syntax

["command-path]msmdarch["] /r server "data-path" "archive-path-and-file-name" ["log-path-and-file-name"]

command-path
The path containing the msmdarch.exe file. By default, this path is:

\Program Files\OLAP Services\Bin\

server
The server computer name to which you want to restore the database.
data-path
The path of the Data directory that will contain the files for the database after the restoration is complete. By default, this path is:

\Program Files\OLAP Services\Data\

archive-path-and-file-name
The path, file name, and .cab extension of the archive file.
log-path-and-file-name
The path, file name, and .log extension of the restore log. If you specify a restore log that already exists, the new restore log is appended to it. If you specify an invalid path or file name, the restore log is written to the \Bin\DBRestore.log file, which by default is ..\Program Files\OLAP Services\Bin\DBRestore.log.
Example

The following command restores the sample FoodMart database included in OLAP Services.

"\Program Files\OLAP Services\Bin\msmdarch" /r myserver "\Program Files\OLAP Services\Data\" "\My archives\server myserver\FoodMart.cab"


Remote Server Data Directory Dialog Box

This dialog box enables you to confirm or change the path of the Data directory to which a Microsoft® SQL Server™ OLAP Services database will be restored.

This dialog box is displayed when in the OLAP Manager tree view you right-click a remote server and click Restore Database.

For more information about restoring OLAP Services databases, see Archive and Restore Databases Add-in.

Options

Path

Specify the path of the Data directory that will contain the files for the database after the restoration is complete.

The Data directory is created during installation of Microsoft® SQL Server™ OLAP Services and contains a directory for each database on the OLAP server. The default path of the Data directory is:

..\Program Files\OLAP Services\Data\

The \Program Files\OLAP Services\ portion of the path can be changed. (To determine the current value, right-click the remote server, click Properties, and see the Data folder box.)

In the Path box, you must precede the path of the Data directory with the remote server name. If the Data directory is shared at its disk level, you must also include the share name associated with the disk. The following example Path value includes the server name Server-1 and the disk share name C$.

\\Server-1\C$\Program Files\OLAP Services\Data\

Browse

Displays a dialog box in which you can browse available paths and select one.

OK

Selects the path in the Path box and continues the restoration procedure.

If OK is dimmed, the path in the Path box is invalid or you do not have authorization to access it.

Cancel

Cancels the restoration.

Help

Displays this Help topic.


Open Archive File Dialog Box

This dialog box enables you to specify the path and file name of the archive file to be used to restore the Microsoft® SQL Server™ OLAP Services database.

This dialog box is displayed when you perform either of the following actions:

  • In the OLAP Manager tree view, right-click a local server, and then click Restore Database.
  • In the Remote Server Data Directory dialog box, click OK.

For more information about restoring OLAP Services databases, see Archive and Restore Databases Add-in.

Options

Look in

Specify the directory containing the archive file. Use the down arrow and up arrow buttons to navigate the directory tree.

File name

Specify the file name of the archive file.

Files of type

Lists the extension of the archive file. The extension must be .cab.

Open

Selects the archive file in the File name box and continues the restoration procedure.

Cancel

Cancels the restoration.


Restore Database Dialog Box

This dialog box enables you to begin the restoration of a Microsoft® SQL Server™ OLAP Services database.

This dialog box is displayed when in the Open Archive File dialog box you click Open.

Caution When you restore a database, its file set (in the Data directory and its subdirectories) and its metadata are returned to their states at the time the archive file was created. Files that were created since then in these directories are deleted. Changes and additions since then to OLAP Services repository records for the database and it objects are removed.

The restoration of a database overwrites the contents of the directory used by the database. The files in this directory are overwritten by the files in the archive file you selected in the Open Archive File dialog box. Files that were added to this directory since the archive file was created are deleted. This directory is named the same as the database and is in the Data directory, which contains a directory for each database on the server. For example, if you restore the FoodMart database, the contents of the ...\Data\FoodMart\ directory (and its subdirectories) are overwritten. The default path of the Data directory is:

..\Program Files\OLAP Services\Data\

The path leading to the Data directory can be changed. (To determine the current path, in the OLAP Manager, right-click the server that contains the database you are restoring, click Properties, and then see the Data folder box.)

When you restore a database, the records in the OLAP Services repository that are associated with the database and its objects are replaced by the OLAP Services repository records in the archive file you selected. By default the OLAP Services repository is:

..\Program Files\OLAP Services\Bin\msmdrep.mdb

However, the path of the repository can be changed at installation, and the repository can be migrated to a SQL Server database.

For more information about restoring OLAP Services databases, see Archive and Restore Databases Add-in.

Options

Restore

Begins the restoration. The Restore Database Progress dialog box, in which you can monitor or cancel the restoration, is displayed.

Cancel

Cancels the restoration.

Help

Displays this Help topic.


Restore Database Progress Dialog Box

This dialog box enables you to monitor the restoration of a Microsoft® SQL Server™ OLAP Services database. You can also cancel the restoration or save the restore log.

This dialog box is displayed when in the Restore Database dialog box you click Restore.

For more information about restoring OLAP Services databases, see Archive and Restore Databases Add-in.

Options

Save Log

Displays a dialog box in which you specify the path and file name of the restore log and then save it. If the restoration completes successfully, the restore log contains a list of the restored files. Otherwise, the restore log contains one or more messages. The Save Log button is available only after the restoration is completed or canceled.

Cancel

Cancels the restoration. If you click Cancel and receive the “Extraction canceled” message, nothing has been restored. A delay may occur between clicking Cancel and display of the message.

The Cancel button is replaced by the Close button after the restoration is completed or canceled.

Close

Closes the dialog box. The Close button is available only after the restoration is completed or canceled.

Help

Displays this Help topic.


Calculated Member Manager Add-in

The Calculated Member Manager Add-in enables you to use the OLAP Manager to create and maintain calculated members in virtual cubes. You can both import calculated members into virtual cubes from their component cubes and create new calculated members directly in virtual cubes. With this add-in, you can use the Calculated Member Builder to create and edit calculated members in virtual cubes.

This add-in includes the Calculated Member Manager, which you can use to maintain the calculated members in a virtual cube. Similar in appearance and function to the Cube editor, the Calculated Member Manager includes three panes:

  • List pane: lists the calculated members in a virtual cube.
  • Properties pane: displays the properties of the calculated member selected in the List pane. Property values can be changed here.
  • Browse pane: displays virtual cube data in the same way the Cube browser displays cube data. This pane contains the familiar data browser used throughout the OLAP Manager.

In addition, the Calculated Member Manager provides single-click access to the Calculated Member Builder and the Import Calculated Members dialog box, which is used to import calculated members into virtual cubes.

For general information about calculated members, see “Calculated Members” and “Creating Calculated Members” in OLAP Services Books Online (msdssbol.chm).

Note This add-in provides features for maintaining calculated members in virtual cubes only. The standard tools and practices for maintaining calculated members in regular cubes are not changed by this add-in.

A virtual cube must be processed before you can maintain its calculated members in the Calculated Member Manager.

For information about the standard Microsoft® SQL Server™ OLAP Services functions used in calculated members, see Function List.


Maintaining Calculated Members in Virtual Cubes

The Calculated Member Manager Add-in provides three basic procedures for maintaining calculated members in virtual cubes:

  • Importing a calculated member into a virtual cube

    Use this procedure to copy one or more calculated members from a virtual cube’s component cubes to the virtual cube.

  • Editing a calculated member in a virtual cube

    Use this procedure to update a virtual cube’s calculated members using the Calculated Member Builder.

  • Creating a calculated member in a virtual cube

    Use this procedure to add new calculated members to a virtual cube using the Calculated Member Builder.


Importing a Calculated Member into a Virtual Cube

To import a calculated member into a virtual cube, use the Calculated Member Manager and the Import Calculated Members dialog box.

To import a calculated member into a virtual cube

  1. In the OLAP Manager tree view, right-click the virtual cube, and then click Manage Calculated Members.
  2. In the Calculated Member Manager, on the File menu, click Import Calculated Member.
  3. In the Import Calculated Members dialog box, select the check box beside the calculated member, and then click OK. (To select all the calculated members in a cube, select the check box beside the cube.)
  4. In the Calculated Member Manager, on the File menu, click Save.

You can import only calculated members that are in the virtual cube’s component cubes.

If you import a calculated member with the same name as a calculated member already in the virtual cube, the imported calculated member is given a new name consisting of its old name and a numeric suffix.

When you import a calculated member, you will see an error message if the calculated member’s value expression includes a measure, dimension, or calculated member that is not in the virtual cube. (The value expression is displayed beside Value in the Calculated Member Manager’s Properties pane.) A calculated member with this type of error is represented in the Calculated Member Manager’s List pane by an icon with a red circle and white X. The calculated member is not included in the cube data displayed to end users as long as the error exists. To correct the error, you can:

  • Add the missing measure, dimension, or calculated member to the virtual cube and process it. To add a measure or dimension, in the OLAP Manager tree view, right-click the virtual cube, click Edit, and use the Virtual Cube wizard. To add a calculated member, import it as described in the procedure earlier in this topic.

    -Or-

  • Edit the calculated member with the error and remove from its value expression the measure, dimension, or calculated member that is not in the virtual cube. For more information, see Editing a Calculated Member in a Virtual Cube.


Editing a Calculated Member in a Virtual Cube

To edit a calculated member in a virtual cube, use the Calculated Member Manager and Calculated Member Builder.

To edit a calculated member in a virtual cube

  1. In the OLAP Manager tree view, right-click the virtual cube, and then click Manage Calculated Members.
  2. In the Calculated Member Manager, right-click the calculated member, and then click Edit Calculated Member.
  3. In the Calculated Member Builder:
    1. In the Parent dimension box, select the dimension that will include the calculated member, or select Measures.
    2. In the Parent member box, specify the member that will include the calculated member. Click Change to select a member other than the displayed member. (Change is unavailable if you selected a one-level dimension or Measures in Step a.)
    3. In the Value expression box, construct an expression to produce the values of the calculated member. Use any combination of the following methods to add to the expression:
    • Drag items from the Data and Functions boxes.
    • Click an item in the Data or Functions box, and then click Insert.
    • Click the arithmetic operator and number buttons.
    • Type. This method is required to add functions from libraries other than the OLAP Services function library.
    4. To register additional function libraries, click Register.
    5. To close the Calculated Member Builder, click OK.
  4. To save the calculated member, in the Calculated Member Manager, on the File menu, click Save.

Note Calculated members in virtual cubes are not visible in the OLAP Manager tree view or HTML pane. To create and access them, you must first display the Calculated Member Manager.


Creating a Calculated Member in a Virtual Cube

To create a calculated member in a virtual cube, use the Calculated Member Manager and Calculated Member Builder.

To create a calculated member in a virtual cube

  1. In the OLAP Manager tree view, right-click the virtual cube, and then click Manage Calculated Members.
  2. In the Calculated Member Manager, on the File menu, click New Calculated Member.
  3. In the Calculated Member Builder:
    1. In the Parent dimension box, select the dimension that will include the calculated member, or select Measures.
    2. In the Parent member box, specify the member that will include the calculated member. Click Change to select a member other than the displayed member. (Change is unavailable if you selected a one-level dimension or Measures in Step a.)
    3. In the Member name box, type a name for the calculated member.
    4. In the Value expression box, construct an expression to produce the values of the calculated member. Use any combination of the following methods to add to the expression:
    • Drag items from the Data and Functions boxes.
    • Click an item in the Data or Functions box, and then click Insert.
    • Click the arithmetic operator and number buttons.
    • Type. This method is required to add functions from libraries other than the OLAP Services function library.
    5. To register additional function libraries, click Register.
    6. To close the Calculated Member Builder, click OK.
  4. To save the calculated member, in the Calculated Member Manager, on the File menu, click Save.

Note Calculated members in virtual cubes are not visible in the OLAP Manager tree view or HTML pane. To create and access them, you must first display the Calculated Member Manager.


Dialog Boxes

The Calculated Member Manager Add-in includes the following dialog boxes:

  • Calculated Member Manager
  • Import Calculated Members dialog box
  • Calculated Member Builder
  • Select the Parent Member dialog box
  • Register Function Libraries dialog box


Calculated Member Manager

The Calculated Member Manager enables you to maintain calculated members in virtual cubes. With the Calculated Member Manager and connected dialog boxes, you can perform the following procedures:

  • Importing a calculated member into a virtual cube
  • Editing a calculated member in a virtual cube
  • Creating a calculated member in a virtual cube

The Calculated Member Manager appears when in the OLAP Manager tree view you right-click a virtual cube and click Manage Calculated Members.

For detailed information about the Calculated Member Manager, select from the following topics:

  • Menus
  • Toolbar
  • List Pane
  • Properties Pane
  • Browse Pane


Menus

The Calculated Member Manager has the following menus.

Menu | Option | Description
File | New Calculated Member | Displays the Calculated Member Builder so you can create a calculated member.
 | Import Calculated Member | Displays the Import Calculated Members dialog box so you can import calculated members from the virtual cube’s component cubes.
 | Save | Saves the virtual cube with the currently defined calculated members.
 | Exit | Closes the Calculated Member Manager.
Edit | Calculated Member | Displays the Calculated Member Builder with the selected calculated member so you can edit it.
 | Rename | Renames the selected calculated member.
 | Delete | Deletes the selected calculated member.
Help | Help on Calculated Member Manager | Displays the Calculated Member Manager Help topic.
 | OLAP Services Books Online | Opens Microsoft® SQL Server™ OLAP Services documentation.


Toolbar

The Calculated Member Manager has the following toolbar.

Button | Description
New Calculated Member | Displays the Calculated Member Builder so you can create a calculated member.
Edit Calculated Member | Displays the Calculated Member Builder with the selected calculated member so you can edit it.
Save | Saves the virtual cube with the currently defined calculated members.
Import Calculated Member | Displays the Import Calculated Members dialog box so you can import calculated members from the virtual cube’s component cubes.


List Pane

The Calculated Member Manager includes the List pane, which lists the calculated members in the virtual cube. Right-click a calculated member to see a shortcut menu.

Shortcut Menu Option | Description
New Calculated Member | Displays the Calculated Member Builder so you can create a calculated member.
Import Calculated Member | Displays the Import Calculated Members dialog box so you can import calculated members from the virtual cube’s component cubes.
Edit Calculated Member | Displays the Calculated Member Builder with the selected calculated member so you can edit it.
Rename | Renames the selected calculated member.
Delete | Deletes the selected calculated member.

Calculated members with creation errors are represented by an icon with a red circle and white X. The errors are displayed when you import the calculated members and later when you open the Calculated Member Manager. You can also display them after you have opened the Calculated Member Manager.

To display the creation error in a calculated member

  1. In the Calculated Member Manager’s List pane, right-click the calculated member, and then click Edit Calculated Member.
  2. In the Calculated Member Builder, click OK.

The most common cause of this kind or error is that the calculated member’s value expression includes a measure, dimension, or calculated member that is not in the virtual cube. For more information, see Importing a Calculated Member into a Virtual Cube.


Properties Pane

The Calculated Member Manager includes the Properties pane, which displays the properties of the calculated member selected in the List pane. Use the Properties pane to modify the property settings for the selected calculated member.

Property | Description
BackColor | Background color of the displayed calculated member. Only numeric values are valid.*
FontFlags | Font flags of the displayed calculated member. Only numeric values are valid.*
FontName | Font of the displayed calculated member.*
FontSize | Font size of the displayed calculated member. Only numeric values are valid.*
ForeColor | Foreground color of the displayed calculated member. Only numeric values are valid.*
Format String | Microsoft® Office format string for displaying cell values. A list of common format strings is displayed when you click Format String. For more information about format strings, see the OLE DB 2.0 and later documentation.
Name | Name of calculated member. The name is displayed in cube browsers.
Parent Dimension | Dimension that includes the calculated member. If the calculated member is a measure, specify Measures.
Parent Member | Member that includes the calculated member. The parent member determines the location of the calculated member in the dimension structure. This property must be null if the parent dimension is a one-level dimension or Measures.
Solve Order | Order in which the calculated member is resolved in case of intersection with other calculated members. Valid values are 0 (zero) and positive integers. Calculated members with lower Solve Order values have precedence in resolution.
Value | MDX expression that defines the values of the calculated member. The values are displayed in cube browsers.
*To use this optional property, the client application must support its use and interpret its values. The Cube browser and the Browse panes of the Cube editor and Calculated Member Manager do not support this property.


Browse Pane

The Calculated Member Manager includes the Browse pane, which organizes the virtual cube data in the data grid. The default view for the data grid shows the data in table format with one dimension across the column headings and another dimension down the left column. (Measures are treated as a single dimension for this purpose.) The remaining dimensions of the virtual cube are displayed in the slicer pane.

The following example Browse pane shows the sample Warehouse and Sales virtual cube included with Microsoft® SQL Server™ OLAP Services.

The white data grid cells represent the measure values according to the members that appear in the levels boxes for all the dimensions shown in the slicer pane. In the preceding example, the measure values reflect the All (that is, highest level) members of all dimensions in the slicer pane except Time. The levels box for the Time dimension displays 1997, which indicates that the displayed measure values are limited to 1997. Later in this topic, an example shows you how to slice through dimensions in the slicer pane to limit the measure values that are displayed.

Working in the Browse Pane

To browse the virtual cube data in the data grid by any combination of dimensions, drag measures or dimensions from the slicer pane onto either axis on the data grid.

To replace a dimension on the data grid with another dimension

• Drag the dimension from the slicer pane to the dimension in the data grid that you want to replace.

• The pointer is in the correct position when it appears like this:

-Or-

The dimension in the data grid moves up to the slicer pane and the new one takes its place.

To move a dimension to the data grid

• Drag the dimension from the slicer pane to a cell below (or beside) the existing dimension.

• The pointer is in the correct position when it appears like this:

-Or-

The new dimension appears under (or beside) the existing dimension, expanding your data grid.

To change the order of dimensions on the data grid

• Drag the dimension and drop it on the dimension where you want it to be located.

To remove a dimension from the data grid

• Drag the dimension you want to remove from the data grid to the slicer pane.

To drill down into a member

• Double-click a dimension member on the data grid. If the member has members beneath it, they are displayed.

Only the member you double-click is expanded. The other members retain their current levels in the data grid. For example, in the following Browse pane, double-click the Food member to drill down into its child members.

If you want to display all the members in a level, double-click the parent level name in the data grid. Level names are depicted with the shading of a button.

To slice through a dimension

• To change the member in a dimension in the slicer pane, click the down arrow on the levels box, expand the levels, and then select a new member. The virtual cube data in the data grid changes to reflect the change in the dimension member.

The Browse pane has an internal memory limit which you may reach if you attempt to browse too much data or drill down too deeply. When you reach the limit, the following message is displayed:

Unable to display current view of cube.
Unable to Allocate Memory For Flexgrid.

The limit cannot be increased by adding or allocating more memory. If you reach the limit, reduce the amount or depth of data you are attempting to browse or use another browser.


Import Calculated Members Dialog Box

This dialog box enables you to import calculated members into a virtual cube from its component cubes.

This dialog box is displayed when in the Calculated Member Manager you perform one of the following actions:

  • On the File menu, click Import Calculated Member.
  • Click the Import Calculated Member button on the toolbar.
  • In the List pane, right-click a calculated member, and then click Import Calculated Member.

Options

Source cube calculated members

Select the check boxes beside the calculated members you want to import. If you want to import all the calculated members in a cube, select the check box beside the cube.

This box lists only calculated members that are in the virtual cube’s component cubes.

If you select a calculated member with the same name as a calculated member already in the virtual cube, the selected calculated member is imported with a new name consisting of its old name and a numeric suffix.

Value expression

Displays the expression for the selected calculated member. To change this expression in the virtual cube, import the calculated member and edit it in the Calculated Member Builder.

OK

Imports the selected calculated members into the virtual cube.

Cancel

Cancels the import.

Help

Displays this Help topic.


Calculated Member Builder

The Calculated Member Builder enables you to add calculated members to a cube or virtual cube.

Note For information about the standard Microsoft® SQL Server™ OLAP Services functions, see Function List.

This dialog box appears for a virtual cube whenever you do one of the following in the Calculated Member Manager:

  • On the File menu, click New Calculated Member.
  • On the Edit menu, click Calculated Member.
  • On the toolbar, click the New Calculated Member button or Edit Calculated Member button.
  • In the List pane, right-click a calculated member, and then click New Calculated Member or Edit Calculated Member.

This dialog box appears for a regular cube whenever you do one of the following in the Cube editor:

  • On the Insert menu, click Calculated Member.
  • Click the Insert Calculated Member button on the toolbar.
  • In the tree view, right-click either the Calculated Members folder or a calculated member, and then click New Calculated Member.
  • In the tree view, right-click a calculated member, and then click Edit.

Options

Parent dimension

Expand the drop-down list and select the dimension in which this calculated member is to be created.

Parent member

If you select a parent dimension other than Measures that has more than one level, the Change button is enabled. Click Change, and then select a parent member.

Member name

The name of this calculated member (for example, “Average Sale”).

Register

Register an external function type library (*.olb, *.tlb, *.dll). The new library will appear in the Functions box.

Value expression

Build the calculated member’s expression using the Data box and the Functions box. The Data box contains the cube’s dimensions, measures, and existing calculated members. The Functions box contains the standard OLAP Services function library plus any additional libraries that have been registered. For information about the OLAP Services functions, see Function List. For information about an individual OLAP Services function, select it in the Functions box, and then press F1.

Note The Data and Functions boxes provides a convenient means for building valid value expressions. You can also type an expression directly into the Value expression box.

To add a function to the value expression

  1. In the Value expression box, place the cursor where you want to insert the function.
  2. Select the desired function in the Functions box.
  3. Click Insert (located above the calculator pad).

The function syntax appears in the Value expression box. Replace any arguments with the appropriate expressions.

Example

In the sample Warehouse and Sales virtual cube, to manually enter a calculated member that finds the average profit margin by store, defined as 1 - Store Cost/Store Sales, follow these steps:

  1. In the Value expression box, type “1-”.
  2. In the Data box, expand Measures and MeasuresLevel to display the individual measures.
  3. Select Store Cost, and then click Insert.
  4. In the Value expression box, place the cursor at the end of the phrase that was just entered, and then type “/”.
  5. In the Data box, select Store Sales.
  6. Click Insert.

    The following expression appears in the Value expression box:

    1-[Measures].[Store Cost]/[Measures].[Store Sales]

  7. In the Member name box, type “Average Store Margin”, and then click OK.


Function List

Usage Notes

  • Null keyword

    You can use the keyword Null to create a calculated member whose value is null.

  • Solve Order

    When you create a calculated member, you can specify an integer value for its Solve Order property in the Advanced tab of the Properties pane. This property specifies a priority for solving calculated members, zero being the highest priority. The order of solving calculated members becomes an issue for cells at intersections where two or more calculated members are involved and the result depends on the order in which the calculated members are solved. For example, if a calculated member for Annual Growth is presented in columns and Performance is presented in rows, the cell at the intersections can mean Annual Growth of Performance or Performance of Annual Growth, depending on which calculated member is solved first.

  • Temporary alias for a set

    A set in a function can be given a temporary alias for use within the function by using the keyword As. In this example, the set {State} is given the temporary alias S1 so it can be referred to later in the function in a nested iteration.

    Sum({State} As S1, Sum(Geography.CurrentMember.Children,
    Population * Val((Geography.CurrentMember.Properties("CityTax")) +
    Val(S1.Current.Properties("StateTax")))))

  • Sample set for examples

    For many expression examples in the following sections, SampleSet is defined to be the following:

    {USA, Buffalo, France, NYC, London, California, LA, Nice, UK, Paris}

    with sales data as indicated in the following table.

Location | 1995 sales | 1996 sales
UK | 1900 | 1700
London | 250 | 300
France | 2500 | 2500
Paris | 365 | 250
Nice | 27 | 100
USA | 5000 | 6500
NYC | 900 | 1100
Buffalo | 300 | 200
California | 2000 | 3500
LA | 500 | 900

ARRAY FUNCTIONS

Function | Syntax | Description
SetToArray | SetToArray(«Set»[, «Set»]...[, «Numeric Expression»]) | Converts one or more sets to an array for use in a user-defined function.

DIMENSION FUNCTIONS

Function | Syntax | Description
Dimension | «Hierarchy».Dimension | Returns the dimension that contains a specified hierarchy.
Dimension | «Level».Dimension | Returns the dimension that contains a specified level.
Dimension | «Member».Dimension | Returns the dimension that contains a specified member.
Dimensions | Dimensions(«Numeric Expression») | Returns the dimension whose zero-based position within the cube is specified by a numeric expression.
Dimensions | Dimensions(«String Expression») | Returns the dimension whose name is specified by a string.

HIERARCHY FUNCTIONS

Function | Syntax | Description
Hierarchy | «Level».Hierarchy | Returns a level's hierarchy.
Hierarchy | «Member».Hierarchy | Returns a member's hierarchy.

LEVEL FUNCTIONS

Function | Syntax | Description
Level | «Member».Level | Returns a member's level.
Levels | «Dimension».Levels («Numeric Expression») | Returns the level whose position in a dimension is specified by a numeric expression.
Levels | Levels(«String Expression») | Returns the level whose name is specified by a string expression.

LOGICAL FUNCTIONS

Function | Syntax | Description
IsEmpty | IsEmpty(«Value Expression») | Determines if an expression evaluates to the empty cell value.

MEMBER FUNCTIONS

Function | Syntax | Description
Ancestor | Ancestor(«Member», «Level») | Returns the ancestor of a member at a specified level.
ClosingPeriod | ClosingPeriod([«Level»[, «Member»]]) | Returns the last sibling among the descendants of a member at a level.
Cousin | Cousin(«Member1», «Member2») | Returns the member with the same relative position under a member as the member specified.
Current Member | «Dimension».Current Member | Returns the current member along a dimension during an iteration.
Default Member | «Dimension».Default Member | Returns the default member of a dimension.
FirstChild | «Member».FirstChild | Returns the first child of a member.
FirstSibling | «Member».FirstSibling | Returns the first child of the parent of a member.
Item | «Tuple».Item(«Numeric Expression») | Returns a member from a tuple.
Lag | «Member».Lag(«Numeric Expression») | Returns a member prior to the specified member along the member's dimension.
LastChild | «Member».LastChild | Returns the last child of a member.
LastSibling | «Member».LastSibling | Returns the last child of the parent of a member.
Lead | «Member».Lead(«Numeric Expression») | Returns a member further along the specified member's dimension.
Members | Members(«String Expression») | Returns the member whose name is specified by a string expression.
NextMember | «Member».NextMember | Returns the next member in the level that contains a specified member.
OpeningPeriod | OpeningPeriod([«Level»[, «Member»]]) | Returns the first sibling among the descendants of a member at a level.
ParallelPeriod | ParallelPeriod([«Level»[, «Numeric Expression»[, «Member»]]]) | Returns a member from a prior period in the same relative position as a specified member.
Parent | «Member».Parent | Returns the parent of a member.
PrevMember | «Member».PrevMember | Returns the previous member in the level that contains a specified member.
ValidMeasure | ValidMeasure(«Tuple») | Returns a valid measure in a virtual cube by forcing inapplicable dimensions to their top level.

NUMERIC FUNCTIONS

Function | Syntax | Description
Aggregate | Aggregate(«Set»[, «Numeric Expression»]) | Returns a calculated value using the appropriate aggregate function, based on the context of the query.
Avg | Avg(«Set»[, «Numeric Expression»]) | Returns the average value of a numeric expression evaluated over a set.
CoalesceEmpty | CoalesceEmpty(«Numeric Expression»[, «Numeric Expression»]...) | Coalesces an empty cell value to a number.
Correlation | Correlation(«Set», «Numeric Expression»[, «Numeric Expression»]) | Returns the correlation of two series evaluated over a set.
Count | Count(«Set»[, EXCLUDEEMPTY | INCLUDEEMPTY]) | Returns the number of tuples in a set, empty cells included unless the optional EXCLUDEEMPTY flag is used.
Covariance | Covariance(«Set», «Numeric Expression»[, «Numeric Expression»]) | Returns the covariance of two series evaluated over a set (biased).
CovarianceN | CovarianceN(«Set», «Numeric Expression»[, «Numeric Expression»]) | Returns the covariance of two series evaluated over a set (unbiased).
IIf | IIf(«Logical Expression», «Numeric Expression1», «Numeric Expression2») | Returns one of two numeric values determined by a logical test.
LinRegIntercept | LinRegIntercept(«Set», «Numeric Expression»[, «Numeric Expression»]) | Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b.
LinRegPoint | LinRegPoint(«Numeric Expression», «Set», «Numeric Expression»[, «Numeric Expression»]) | Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b.
LinRegR2 | LinRegR2(«Set», «Numeric Expression»[, «Numeric Expression»]) | Calculates the linear regression of a set and returns R2 (the coefficient of determination).
LinRegSlope | LinRegSlope(«Set», «Numeric Expression»[, «Numeric Expression»]) | Calculates the linear regression of a set and returns the value of a in the regression line y = ax + b.
LinRegVariance | LinRegVariance(«Set», «Numeric Expression»[, «Numeric Expression»]) | Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b.
Max | Max(«Set»[, «Numeric Expression»]) | Returns the maximum value of a numeric expression evaluated over a set.
Median | Median(«Set»[, «Numeric Expression»]) | Returns the median value of a numeric expression evaluated over a set.
Min | Min(«Set»[, «Numeric Expression»]) | Returns the minimum value of a numeric expression evaluated over a set.
Ordinal | «Level».Ordinal | Returns the zero-based ordinal value associated with a level.
Rank | Rank(«Tuple», «Set») | Returns the one-based rank of a tuple in a set.
Stddev | Stddev(«Set»[, «Numeric Expression»]) | Alias for Stdev.
StddevP | StddevP(«Set»[, «Numeric Expression»]) | Alias for StdevP.
Stdev | Stdev(«Set»[, «Numeric Expression»]) | Returns the standard deviation of a numeric expression evaluated over a set (unbiased).
StdevP | StdevP(«Set»[, «Numeric Expression»]) | Returns the standard deviation of a numeric expression evaluated over a set (biased).
Sum | Sum(«Set»[, «Numeric Expression»]) | Returns the sum of a numeric expression evaluated over a set.
Value | «Measure».Value | Returns the value of a measure.
Var | Var(«Set»[, «Numeric Expression»]) | Returns the variance of a numeric expression evaluated over a set (unbiased).
Variance | Variance(«Set»[, «Numeric Expression»]) | Alias for Var.
VarianceP | VarianceP(«Set»[, «Numeric Expression»]) | Alias for VarP.
VarP | VarP(«Set»[, «Numeric Expression»]) | Returns the variance of a numeric expression evaluated over a set (biased).

SET FUNCTIONS

Function | Syntax | Description
AddCalculated
Members 		AddCalculatedMembers
(«Set») 		Adds calculated members to a set.
BottomCount | BottomCount(«Set», «Count»[, «Numeric Expression»]) | Returns a specified number of items from the bottom of a set, optionally ordering the set first.
BottomPercent | BottomPercent(«Set», «Percentage», «Numeric Expression») | Sorts a set and returns the bottom N elements whose cumulative total is at least a specified percentage.
BottomSum | BottomSum(«Set», «Value», «Numeric Expression») | Sorts a set and returns the bottom N elements whose cumulative total is at least a specified value.
Children | «Member».Children | Returns the children of a member.
Crossjoin | Crossjoin(«Set1», «Set2») | Returns the cross product of two sets.
Descendants | Descendants(«Member», «Level»[, «Desc_flag»]) | Returns the set of descendants of a member at a specified level, optionally including or excluding descendants in other levels.
Distinct | Distinct(«Set») | Eliminates duplicate tuples from a set.
DrilldownLevel DrilldownLevel(«Set»[, «Level»])
or
DrilldownLevel(«Set», , «Index») 		Drills down the members of a set, at a specified level, to one level below.

Alternatively, drills down on a specified dimension in the set.
DrilldownLevelBottom DrilldownLevelBottom
(«Set», «Count»[, [«Level»][, «Numeric Expression»]]) 		Drills down the bottom N members of a set, at a specified level, to one level below.
DrilldownLevelTop DrilldownLevelTop
(«Set», «Count»[, [«Level»][, «Numeric Expression»]]) 		Drills down the top N members of a set, at a specified level, to one level below.
Drilldown
Member 		DrilldownMember
(«Set1», «Set2»[, RECURSIVE]) 		Drills down the members in a set that are present in a second specified set.
Drilldown
MemberBottom 		DrilldownMemberBottom(«Set1», «Set2», «Count»[, [«Numeric Expression»][, RECURSIVE]]) Like DrilldownMember except that it includes only the bottom N children.
Drilldown
MemberTop 		DrilldownMemberTop
(«Set1», «Set2», «Count»[, [«Numeric Expression»][, RECURSIVE]]) 		Like DrilldownMember except that it includes only the top N children.
DrillupLevel | DrillupLevel(«Set»[, «Level»]) | Drills up the members of a set that are below a specified level.
DrillupMember | DrillupMember(«Set1», «Set2») | Drills up the members in a set that are present in a second specified set.
Except | Except(«Set1», «Set2»[, ALL]) | Finds the difference between two sets, optionally retaining duplicates.
Extract | Extract(«Set», «Dimension»[, «Dimension»...]) | Returns a set of tuples from extracted dimension elements. The opposite of Crossjoin.
Filter | Filter(«Set», «Search Condition») | Returns the set resulting from filtering a set based on a search condition.
Generate | Generate(«Set1», «Set2»[, ALL]) | Applies a set to each member of another set and joins the resulting sets by union.
Head | Head(«Set»[, « Numeric Expression »]) | Returns the first specified number of elements in a set.
Hierarchize | Hierarchize(«Set») | Orders the members of a set in a hierarchy.
Intersect | Intersect(«Set1», «Set2»[, ALL]) | Returns the intersection of two input sets, optionally retaining duplicates.
LastPeriods | LastPeriods(«Index»[, «Member»]) | Returns a set of members prior to and including a specified member.
Members | «Dimension».Members | Returns the set of all members in a dimension.
Members | «Hierarchy».Members | Returns the set of all members in a hierarchy.
Members | «Level».Members | Returns the set of all members in a level.
Mtd | Mtd([«Member»]) | A shortcut function for the PeriodsToDate function that specifies the level to be Month.
Order | Order(«Set», {«String Expression» | «Numeric Expression»}[, ASC | DESC | BASC | BDESC]) | Arranges members of a set, optionally preserving or breaking the hierarchy.
PeriodsToDate | PeriodsToDate([«Level»[, «Member»]]) | Returns a set of periods (members) from a specified level starting with the first period and ending with a specified member.
Qtd | Qtd([«Member»]) | A shortcut function for the PeriodsToDate function that specifies the level to be Quarter.
StripCalculatedMembers StripCalculatedMembers
(«Set») 		Removes calculated members from a set.
StrToSet | StrToSet(«String Expression») | Constructs a set from a string expression.
Subset | Subset(«Set», «Start»[, «Count»]) | Returns a subset of elements from a set.
Tail | Tail(«Set»[, «Count»]) | Returns a subset from the end of a set.
ToggleDrillState | ToggleDrillState(«Set1», «Set2»[, RECURSIVE]) | Toggles the drill state of members. This function is a combination of DrillupMember and DrilldownMember.
TopCount | TopCount(«Set», «Count»[, «Numeric Expression»]) | Returns a specified number of items from the top of a set, optionally ordering the set first.
TopPercent | TopPercent(«Set», «Percentage», «Numeric Expression») | Sorts a set and returns the top N elements whose cumulative total is at least a specified percentage.
TopSum | TopSum(«Set», «Value», «Numeric Expression») | Sorts a set and returns the top N elements whose cumulative total is at least a specified value.
Union | Union(«Set1», «Set2»[, ALL]) | Returns the union of two sets, optionally retaining duplicates.
VisualTotals | VisualTotals(«Set», «Pattern») | Dynamically totals child members specified in a set using a pattern for the total label in the result set.
Wtd | Wtd([«Member»]) | A shortcut function for the PeriodsToDate function that specifies the level to be Week.
Ytd | Ytd([«Member»]) | A shortcut function for the PeriodsToDate function that specifies the level to be Year.

STRING FUNCTIONS

Function | Syntax | Description
CoalesceEmpty | CoalesceEmpty(«String Expression»[, «String Expression»]...) | Coalesces an empty cell value to a string.
IIf | IIf(«Logical Expression», «String Expression1», «String Expression2») | Returns one of two string values determined by a logical test.
Name | «Dimension».Name | Returns the name of a dimension.
Name | «Hierarchy».Name | Returns the name of a hierarchy.
Name | «Level».Name | Returns the name of a level.
Name | «Member».Name | Returns the name of a member.
SetToStr | SetToStr(«Set») | Constructs a string from a set.
TupleToStr | TupleToStr(«Tuple») | Constructs a string from a tuple.
UniqueName | «Dimension».UniqueName | Returns the unique name of a dimension.
UniqueName | «Level».UniqueName | Returns the unique name of a level.
UniqueName | «Member».UniqueName | Returns the unique name of a member.

TUPLE FUNCTIONS

Function | Syntax | Description
Current | «Set».Current | Returns the current tuple from a set during an iteration.
Item | «Set».Item(«String Expression»[, «String Expression»...] | «Index») | Returns a tuple from a set.
StrToTuple | StrToTuple(«String Expression») | Constructs a tuple from a string.


AddCalculatedMembers

Adds calculated members to a set.

Syntax

AddCalculatedMembers(«Set»)

Remarks

This function includes only the calculated members that are siblings in the set.

Example

In the above diagram, the following functions return the following values.

Function | Returns
AddCalculatedMembers({GrandParent.Children}) | {[Parent 1],[Parent 2],[Calculated member 0]}
AddCalculatedMembers({[Parent 1].Children}) | {[Member 1.1], [Member 1.2], [Member 1.3], [Calculated member 1.4]}
AddCalculatedMembers({[Parent 2].[Member 2.1]}) | {[Member 2.1], [Calculated member 2.4]}


Aggregate

Returns a calculated value using the appropriate aggregate function, based on the aggregation type of the member.

Syntax

Aggregate(«Set»[, «Numeric Expression»])

Remarks

This function cannot be used on calculated members.

Example

Consider the following expression:

WITH MEMBER Geography.Total AS  'AGGREGATE({USA, France})'

SELECT {Measures.SumSales, Measures.MaxSales} ON COLUMNS,

      {USA, France, Total} ON ROWS

FROM SalesCube

WHERE ([1998])

In this expression, the calculated member Total is displayed first against the measure SumSales and then against the measure MaxSales. In the former case, Total is calculated by adding (with Sum) and in the latter case, Total is calculated by taking the maximum.


Ancestor

Returns the ancestor of a member at a specified level.

Syntax

Ancestor(«Member», «Level»)

Example

If the Geography dimension includes levels Country, State, and City, then the following functions return the following values.

Function | Returns
Ancestor(Seattle, Country) | USA
Ancestor(Seattle, State) | Washington


Avg

Returns the average value of a numeric expression evaluated over a set.

Syntax

Avg(«Set»[, «Numeric Expression»])

Remarks

This function requires an implicit count of the number of cells, not including empty cells. In order to force the inclusion of empty cells, the application must use the CoalesceEmpty() function. For more information about empty cells, see the OLE DB documentation.

Example
Function | Returns
Avg({USA, CANADA, MEXICO}, Sales) | 2000

The preceding function returns the preceding value if respective Sales are 1000, 2000, and 3000.


BottomCount

Returns a specified number of items from the bottom of a set, optionally ordering the set first.

Syntax

BottomCount(«Set», «Count»[, «Numeric Expression»])

Remarks

Sorts a set according to the value of «Numeric Expression» and returns the bottom «Count» members, where «Count» is a numeric expression.

This function always breaks the hierarchy. The TopCount function is similar.

Example

Bottomcount(Geography.Cities.Members, 5, Sales)


BottomPercent

Sorts a set and returns the bottom N elements whose cumulative total is at least a specified percentage.

Syntax

BottomPercent(«Set», «Percentage», «Numeric Expression»)

Remarks

Sorts a set using «Numeric Expression» and returns the bottom x elements whose cumulative total of «Numeric Expression» is at least «Percentage». «Percentage» is a numeric expression.

This function always breaks the hierarchy. The TopPercent function is similar.

Example

Bottompercent(Products.[Product Description].Members, 25, Sales)


BottomSum

Sorts a set using a numeric expression and returns the bottom N elements whose sum is at least a specified value.

Syntax

BottomSum(«Set», «Value», «Numeric Expression»)

Remarks

Sorts on «Numeric Expression» and picks up the bottom x (the smallest number possible) elements such that their sum is at least «Value».

This function always breaks the hierarchy. The TopSum function is similar.

Example

Bottomsum(Products.[Product Description].Members, 100000, Quantity)


Children

Returns the children of a member.

Syntax

«Member».Children

Example

[Geography].[All Geography].[Central Region].Children


ClosingPeriod

Returns the last sibling among the descendants of a member at a level.

Syntax

ClosingPeriod([«Level»[, «Member»]])

Remarks

The dimension containing «Level» is used if «Level» is specified; otherwise, the Time dimension is used. If no «Level» is specified, the level below that of «Member» is used. If no «Level» or «Member» is specified, the default is Time.CurrentMember.

This function is equivalent to BottomCount(Descendants(«Member», «Level»), 1).

The function OpeningPeriod is similar, the only difference being that it returns the first sibling instead of the last sibling.

Example

ClosingPeriod(Month, [1991])

returns [1991].December.


CoalesceEmpty

Coalesces an empty cell value to a number or a string and returns the coalesced value.

Syntax

CoalesceEmpty(«Value Expression»[, «Value Expression»]...)

Remarks

Returns the first (from the left) non-empty value expression in the list of value expressions. Returns the empty cell value if all value expressions evaluate to the empty cell value.

All value expressions must evaluate to the same type, numeric or string, or to the empty cell value.

For more information about empty cells, see the OLE DB documentation.

Example

To string:

CoalesceEmpty(Time.Parent.Name, "EMPTY")

Returns “EMPTY” if Time.Parent.Name is the empty cell value, Time.Parent.Name otherwise.

To number:

CoalesceEmpty(Measures.CurrentMember, -99)

Returns -99 if Measures.CurrentMember is the empty cell value, Measures.CurrentMember otherwise.


Correlation

Returns the correlation of two series evaluated over a set.

Syntax

Correlation(«Set», «Numeric Expression»[, «Numeric Expression»])

Remarks

Evaluates «Set» against the first «Numeric Expression» to get the values for the y-axis. «Set» is evaluated against the second «Numeric Expression», if present, to get the set of values for the x-axis. If the second «Numeric Expression» is not present, the members of «Set» are used as values for the x-axis.

Note Empty cells or cells containing text or logical values are ignored; however, cells with zero values are included.


Count

Returns the number of tuples in a set, empty cells included unless the optional EXCLUDEEMPTY flag is used.

Syntax

Count(«Set»[, EXCLUDEEMPTY | INCLUDEEMPTY])

Note Empty cells are counted by default. The comparable Count function in OLE DB excludes empty cells by default.

Remarks

To exclude empty cells, use the optional EXCLUDEEMPTY keyword.

Example

If Time has levels Year and Month, and the members of Year are 1994 and 1995, then:

Count({Time.Month.Members})

returns 24.


Cousin

Returns the member with the same relative position under a member as the member specified.

Syntax

Cousin(«Member1», «Member2»)

Remarks

This function operates on the position of members within levels. In the example below it is assumed that both years 1996 and 1994 contain the same number of months preceding the member March. However, if 1996 contained the members January, February, March, and so forth, and 1994 contained the members January, March, April, and so forth, then the example would return [1994].April because it is in the same relative position (third) within the year.

Example

Cousin([1996].March, [1994])

This example yields the member [1994].March.


Covariance

Returns the covariance of two series evaluated over a set using the biased population.

Syntax

Covariance(«Set», «Numeric Expression»[, «Numeric Expression»])

Remarks

Evaluates «Set» against the first «Numeric Expression» to get the values for the y-axis. «Set» is evaluated against the second «Numeric Expression», if present, to get the set of values for the x-axis. If the second «Numeric Expression» is not present, the members of «Set» are used as values for the x-axis.

Covariance uses the biased population formula. CovarianceN uses the unbiased population formula.

Note Empty cells or cells containing text or logical values are ignored; however, cells with zero values are included.


CovarianceN

Returns the covariance of two series evaluated over a set using the unbiased population formula.

Syntax

Covariance(«Set», «Numeric Expression»[, «Numeric Expression»])

Remarks

Evaluates «Set» against the first «Numeric Expression» to get the values for the y-axis. «Set» is evaluated against the second «Numeric Expression», if present, to get the set of values for the x-axis. If the second «Numeric Expression» is not present, the members of «Set» are used as values for the x-axis.

CovarianceN uses the unbiased population formula. Covariance uses the biased population formula.

Note Empty cells or cells containing text or logical values are ignored; however, cells with zero values are included.


Crossjoin

Returns the cross product of two sets.

Syntax

Crossjoin(«Set1», «Set2»)

Remarks

The order of tuples in the resulting set is as follows:

If «Set1» = {x1, x2,...,xn} and «Set2» = {y1, y2, ..., yn}, then Crossjoin(Set1, Set2) is:

{(x1, y1), (x1, y2),...,(x1, yn), (x2, y1), (x2, y2),...,

(x2, yn),..., (xn, y1), (xn, y2),..., (xn, yn)}

Example

Crossjoin({[1994], [1995]}, {USA, Japan})

returns

{([1994], USA), ([1994], Japan), ([1995], USA), ([1995], Japan)}.

Alternate Syntax

«Set1» * «Set2»

Note This alternate syntax is a Microsoft® SQL Server™ OLAP Services multidimensional expression (MDX) extension to OLE DB 2.0 and later.


Current

Returns the current tuple from a set during an iteration.

Syntax

«Set».Current

Remarks

During an iteration through a set, as in functions such as Generate, at each step in the iteration the tuple being operated upon is the current tuple. This function returns that tuple.


CurrentMember

Returns the current member along a dimension during an iteration.

Syntax

«Dimension».CurrentMember

Remarks

During an iteration through a set of dimension members, at each step in the iteration the member being operated upon is the current member. This function returns that member.


DefaultMember

Returns the default member of a dimension.

Syntax

«Dimension».DefaultMember

Remarks

The default member is usually All or one of the top members in the dimension. If there is no All level, DefaultMember is ambiguous.

Example

If the Time dimension has levels (All), Year, Quarter, and Month, and member All-Time, then:

Time.DefaultMember

returns All-Time.


Descendants

Returns the set of descendants of a member at a specified level, optionally including or excluding descendants in other levels.

Syntax

Descendants(«Member», «Level»[, «Desc_flag»])

Flags
Flag | Description
SELF | Default. Returns descendant members from «Level» only. Includes «Member», if and only if «Level» specified is the level of «Member».
AFTER | Returns descendant members from all levels subordinate to «Level».
BEFORE | Returns descendant members from all levels between «Member» and «Level», not including members from «Level».
BEFORE_AND_AFTER | Returns descendant members from all levels subordinate to the level of «Member» except members from «Level».
SELF_AND_AFTER | Returns descendant members from «Level» and all levels subordinate to «Level».
SELF_AND_BEFORE | Returns descendant members from «Level» and all levels between «Member» and «Level».
SELF_BEFORE_AFTER | Returns descendant members from all levels subordinate to the level of «Member».

Remarks

By default, only members at the specified level will be included. This function corresponds to a «Desc_flag» value of SELF. By changing the value of «Desc_flag», you can include or exclude descendants at «Level», the children before «Level», and the children after «Level» (until the leaf node).

Example

Assume levels are Countries, States, Counties, and Cities.

Function | Returns
Descendants(USA, Counties) | All counties in USA.
Descendants(USA, Counties, SELF) | All counties in USA.
Descendants(USA, Counties, BEFORE) | All states in USA.
Descendants(USA, Counties, AFTER) | All cities in USA.
Descendants(USA, Counties, BEFORE_AND_AFTER) | All states and cities in USA.
Descendants(USA, Counties, SELF_BEFORE_AFTER) | All states, counties, and cities in USA.


Dimension

Returns the dimension that contains a specified member, level, or hierarchy.

 | Members | Levels | Hierarchies
Syntax | «Member».Dimension | «Level».Dimension | «Hierarchy». Dimension
Remarks | Returns the dimension that contains «Member». | Returns the dimension that contains «Level». | Returns «Hierarchy».
Example | [1998].Dimension returns Time. | Year.Dimension returns Time. | FiscalYear. Dimension returns FiscalYear.

Note Microsoft® SQL Server™ OLAP Services implements hierarchies as separate dimensions, so «Hierarchy».Dimension returns «Hierarchy».


Dimensions

Returns the dimension specified by a numeric expression or a string.

Numeric String
Syntax Dimensions
(«Numeric Expression») 		Dimensions
(«String Expression»)
Remarks Returns the dimension object
whose zero-based position within the cube is «Numeric Expression». 		Returns the dimension whose
name is specified by «String Expression». Typically used
with user-defined functions.
Example If dimensions Time, Region,
and Product are added to a cube
(in that order), then

Dimensions(2)

returns Region (Measures is
always Dimensions(0)).


Distinct

Eliminates duplicate tuples from a set.

Syntax

Distinct(«Set»)

Remarks

Duplicates are eliminated from the tail.

Example

Distinct({(a,b), (c,d), (a,b)})

returns

{(a,b), (c,d )}.


DrilldownLevel

Drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set.

Syntax

DrilldownLevel(«Set»[, «Level»])

Remarks

The members that result from the drill down are hierarchized to occur under their parents. Order is preserved among the original members in «Set».

If «Level» is not specified, it is assumed to be the level of the lowest level member of «Set».

If «Level» is specified and there is no member of «Level» represented in «Set», «Set» is returned.

Alternate Syntax

DrilldownLevel(«Set», , «Index»)

Remarks

When drilling down in sets of tuples, the dimension to be drilled down can be specified by its zero-based numeric position in the tuples instead of by level. For example, in the set:

(Crossjoin(Crossjoin([Store Country].members,[Product Family].members),[Year].members)

a sample tuple is (Canada, Drink, 1998). The Store dimension is position 0, Product dimension is position 1, and Time dimension is position 2. The following expression drills down on the Product dimension:

DrilldownLevel(Crossjoin(Crossjoin([Store Country].members,[Product Family].members),[Year].members),,1)

Note The zero-based «Index» parameter is a Microsoft® SQL Server™ OLAP Services multidimensional expression (MDX) extension to OLE DB.

Example

DrilldownLevel({[Customers Location].USA, [Customers Location].CA, [Customers Location].WA, [Customers Location].Canada})

or, equivalently,

DrilldownLevel({[Customers Location].USA, [Customers Location].CA, [Customers Location].WA, [Customers Location].Canada}, [Customers Location].[State Province])

returns the set

{USA, CA, <all cities in CA>, WA,

<all cities in WA>, Canada}

whereas

DrilldownLevel({[Customers Location].USA, [Customers Location].CA, [Customers Location].WA, [Customers Location].Canada}, [Customers Location].[Country])

returns the set

{USA, CA, WA, Canada, BC}.

For more information, see the OLE DB documentation.


DrilldownLevelBottom

Drills down the bottom N members of a set, at a specified level, to one level below.

Syntax

DrilldownLevelBottom(«Set», «Count»[, [«Level»][, «Numeric Expression»]])

Remarks

DrilldownLevelBottom functions like DrilldownLevel but instead of including all children for each member at the specified «Level», only the bottom «Count» number of children are returned, based on «Numeric Expression».

Example

Assuming OR and WA had the lowest sales,

DrilldownLevelBottom({[Customers Location].USA},2,,[Unit Sales])

returns the set

{USA, OR, WA}.

The expression

DrilldownLevelBottom({[Customers Location].[Country].Members},2,,[Unit Sales])

returns the set

{Canada, <bottom two provinces in Canada>, Mexico, <bottom two states in Mexico>, ..., USA, <bottom two states in USA>}.


DrilldownLevelTop

Drills down the top N members of a set, at a specified level, to one level below.

Syntax

DrilldownLevelTop(«Set», «Count»[, [«Level»][, «Numeric Expression»]])

Remarks

DrilldownLevelTop functions like DrilldownLevel does, but instead of including all children for each member at the specified «Level», only the top «Count» number of children are returned, based on «Numeric Expression».

Example

Assuming CA and NY had the highest sales,

DrilldownLevelTop({[Customers Location].USA},2,,[Unit Sales])

returns the set

{USA, CA, NY}.

The expression

DrilldownLevelTop({[Customers Location].[Country].Members},2,,[Unit Sales])

returns the set

{Canada, <top two provinces in Canada>, Mexico, < top two states in Mexico>, ..., USA, < top two states in USA>}.


DrilldownMember

Drills down the members in a specified set that are present in a second specified set.

Alternatively, drills down on a set of tuples.

Syntax

DrilldownMember({«Set1»}, «Set2»[, RECURSIVE])

Remarks

Drills down the members in «Set1» that are present in «Set2». «Set1» is usually a subset of «Set2». If RECURSIVE is specified, the drilldown continues, comparing the expanded the result set against «Set2» at each step.

«Set1» may contain tuples instead of members. Tuple drilldown is an extension of OLE DB, and it returns a set of tuples instead of members.

Example

DrilldownMember({USA, Canada, Mexico}, {USA, WA, Mexico})

returns the set

{USA, <all states in USA>, Canada, Mexico, <all states in Mexico>}

and

DrilldownMember({USA, Canada, Mexico}, {USA, WA, Mexico},RECURSIVE)

returns the set

{USA, <all states in USA before WA>, WA, <all cities in WA>, <all cities in USA after WA>, Canada, Mexico, <all states in Mexico>}.

Tuple drilldown

DrilldownMember({(USA,[Unit Sales]), (Canada,[Unit Sales]), (Mexico,[Unit Sales])},{USA, WA})

returns the set of tuples

{(USA,[Unit Sales]), (AZ,[Unit Sales]), ... , (WY,[Unit Sales]), (Canada,[Unit Sales]), (Mexico,[Unit Sales])}.

DrilldownMember({(USA,[Unit Sales]), (Canada,[Unit Sales]), (Mexico,[Unit Sales])},{USA, WA},RECURSIVE)

returns the set of tuples

{(USA,[Unit Sales]), (AZ,[Unit Sales]), ... (WA,[Unit Sales]), (<City1 in WA>,[Unit Sales]), ... (<CityN in WA>,[Unit Sales]), ... (WY,[Unit Sales]), (Canada,[Unit Sales]), (Mexico,[Unit Sales])}.


DrilldownMemberBottom

Drills down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of members.

Alternatively, drills down on a set of tuples.

Syntax

DrilldownMemberBottom(«Set1», «Set2», «Count»[, «Numeric Expression»][, RECURSIVE]])

Remarks

«Set1» may contain tuples instead of members. Tuple drilldown is an extension of OLE DB, and it returns a set of tuples instead of members.

Functions like DrilldownMember but instead of including all children of a member, only the bottom «Count» number of children are returned, based on «Numeric Expression».

Example

DrilldownMemberBottom({USA, Canada, Mexico}, {USA, WA, Mexico}, 2, [Unit Sales])

returns the set

{USA, <bottom two states in USA>, Canada, Mexico, <bottom two states in Mexico>}

and

DrilldownMemberBottom({USA, WA, Canada, Mexico}, {USA, WA, Mexico}, 2, [Unit Sales], RECURSIVE)

returns the set

{USA, WA, <bottom two cities in WA>, Canada, Mexico, <bottom two states in Mexico>}.

Tuple drilldown

DrilldownMemberBottom({(USA, [Unit Sales]), (WA, [Unit Sales]), (Canada, [Unit Sales]), (Mexico, [Unit Sales])},{USA, WA},2,[Store Sales],RECURSIVE)

returns the set

{(USA, [Unit Sales]), (WA, [Unit Sales]), (<bottom two cities in WA>, [Unit Sales]), (Canada, [Unit Sales]), (Mexico, [Unit Sales]}.


DrilldownMemberTop

Drills down the members in a specified set that are present in a second specified set, limiting the result set to a specified number of members.

Alternatively, drills down on a set of tuples.

Syntax

DrilldownMemberTop(«Set1», «Set2», «Count»[, «Numeric Expression»][, RECURSIVE]])

Remarks

«Set1» may contain tuples instead of members. Tuple drilldown is an extension of OLE DB, and it returns a set of tuples instead of members.

Functions like DrilldownMember does, but instead of including all children of a member, only the top «Count» number of children are returned, based on «Numeric Expression».

Example

DrilldownMemberTop({USA, Canada, Mexico}, {USA, WA, Mexico}, 2, [Unit Sales])

returns the set

{USA, <top two states in USA>, Canada, Mexico, <top two states in Mexico>}

and

DrilldownMemberTop({USA, WA, Canada, Mexico}, {USA, WA, Mexico}, 2, [Unit Sales], RECURSIVE)

returns the set

{USA, WA, <top two cities in WA>, Canada, Mexico, <top two states in Mexico>}.

Tuple drilldown

DrilldownMemberTop({(USA, [Unit Sales]), (WA, [Unit Sales]), (Canada, [Unit Sales]), (Mexico, [Unit Sales])},{USA, WA},2,[Store Sales],RECURSIVE)

returns the set

{(USA, [Unit Sales]), (WA, [Unit Sales]), (<top two cities in WA>, [Unit Sales]), (Canada, [Unit Sales]), (Mexico, [Unit Sales]}.


DrillupLevel

Drills up the members of a set that are below a specified level.

Syntax

DrillupLevel(«Set»[, «Level»])

Remarks

If «Level» is not specified, it is assumed to be one less than the level of the lowest level member in «Set».

Example

DrillUpLevel({USA, CA, [Los Angeles], WA, Seattle, Canada, BC})

returns the set

{USA, CA, WA, Canada, BC}

and

DrillUpLevel({USA, CA, [Los Angeles], WA, Seattle, Canada, BC},[Store Country])

returns the set

{USA, Canada }.


DrillupMember

Drills up the members in a specified set that are present in a second specified set.

Syntax

DrillupMember(«Set1», «Set2»)

Remarks

Drills up the members in «Set1» that are present in «Set2». «Set2» is usually a subset of «Set1».

Example

DrillupMember({Canada, Mexico, USA, WA, Seattle},{WA})

returns the set

{Canada, Mexico, USA, WA}.


Except

Finds the difference between two sets, optionally retaining duplicates.

Syntax

Except(«Set1», «Set2»[, ALL])

Remarks

Duplicates are eliminated from both sets prior to finding the difference. The optional ALL flag retains duplicates. Matching duplicates in «Set1» are eliminated and non-matching duplicates are retained.

Example

Except({Canada, BC, BC, Mexico, USA, WA}, {Canada, Mexico, CA})

returns

{BC, USA, WA}

and

Except({Canada, BC, BC, Mexico, USA, WA}, {Canada, Mexico, CA},ALL)

returns

{BC, BC, USA, WA}.


Extract

Returns a set of tuples from extracted dimension elements. The opposite of Crossjoin.

Syntax

Extract(«Set», «Dimension»[, «Dimension»...])

Remarks

Returns a set consisting of tuples from the extracted «Dimension» elements. This function always removes the duplicates. This function is the opposite of the Crossjoin function.

Example

Extract({([1997], WA), ([1997], CA), ([1998], CA)}, Time)

returns the set

{[1997], [1998]}


Filter

Returns the set resulting from filtering a set based on a search condition.

Syntax

Filter(«Set», «Search Condition»)

Example

Filter(SampleSet, (Sales,[1996]) < (Sales, [1995]))

returns

{Paris, Buffalo}

if these cities declined in sales.


FirstChild

Returns the first child of a member.

Syntax

«Member».FirstChild

Example

If the Time dimension includes the levels Year, Quarter, Month, Week, and Day, then:

[1995].Firstchild

returns January.


FirstSibling

Returns the first child of the parent of a member.

Syntax

«Member».FirstSibling

Example

May.FirstSibling

returns April (assuming that the parent level is quarters).


Generate

Applies a set to each member of another set and joins the resulting sets by union.

Syntax

Generate(«Set1», «Set2»[, ALL])

Remarks

Applies «Set2» to each member of «Set1» and joins the resulting sets by union.

If ALL is specified, duplicates in the result are retained.

Example

Generate({USA, France}, Descendants(Geography.CurrentMember, Cities))

For each member of the set {USA, France}, this function applies the expression Descendants(Geography.CurrentMember, Cities). Each such application results in a set. (Application to USA will generate the set of all cities in USA; application to France will generate all cities in France.) These sets are joined by union to get the result of this function. In this example, all cities in USA and France will be the result. In general, Generate(«Set1», «set_expression») will apply «set_expression» to each member of «Set1» and join the results by union.

If «Set1» is not related to «set_expression» by means of CurrentMember, then Generate results in a simple replication of the set implied by «set_expression», with as many replications as there are tuples in «Set1». If the optional ALL flag is specified, all duplicates are retained in the result. If ALL is not specified, duplicates are removed. For example,

Generate({USA, FRANCE}, {SEATTLE, BOSTON}, ALL)

returns the set

{SEATTLE, BOSTON, SEATTLE, BOSTON}.

However, if ALL was not specified, then the set returned is

{SEATTLE, BOSTON}.


Head

Returns the first specified number of elements in a set.

Syntax

Head(«Set»[, «Numeric Expression»])

Remarks

Returns the first «Numeric Expression» elements in a set. The order of elements is preserved. The default value of «Numeric Expression» is 1. If «Numeric Expression» is less than 1, the empty set is returned. If «Numeric Expression» exceeds the number of tuples in the set, the original set is returned.

Example

Head({USA, Canada, France, Germany, Japan}, 3)

returns the set

{USA, Canada, France}.


Hierarchize

Orders the members of a set in a hierarchy.

Syntax

Hierarchize(«Set»)

Remarks

Orders the members of «Set» in a hierarchy. Members in a level are sorted in their natural order, the default ordering of the members along a dimension when no other sort conditions are specified. Hierarchize will always retain duplicates.

Example

Hierarchize(SampleSet)

returns the set in natural order. The hierarchized dataset follows (assuming that the natural order for the data source is alphabetical):

France |  |
 |  | Nice
 |  | Paris
UK |  |
 |  | London
USA |  |
 | California |
 |  | LA
 |  | Buffalo
 |  | NYC


Hierarchy

Returns the hierarchy that contains a specified member or level.

 | Members | Levels
Syntax | «Member».Hierarchy | «Level».Hierarchy
Remarks | Returns the hierarchy that contains «Member». | Returns the hierarchy that contains «Level».
Example | [January].Hierarchy returns Time.FiscalYear | [Year].Hierarchy returns Time.FiscalYear


IIf

Returns one of two values determined by a logical test.

Syntax

IIf(«Logical Expression», «Value Expression1», «Value Expression2»)

Remarks

Returns «Value Expression1» if «Logical Expression» evaluates to TRUE, otherwise returns «Value Expression2».

«Value Expression1» and «Value Expression2» must evaluate to the same type, either numeric or string.

Example

To string:

IIf(IsEmpty(Measures.CurrentMember), "Yes", "No")

Returns "Yes" if Measures.CurrentMember is an empty cell, "No" otherwise.

To number:

IIf(IsEmpty(Measures.CurrentMember), 0, 1)

Returns 0 if Measures.CurrentMember is an empty cell, 1 otherwise.


Intersect

Returns the intersection of two input sets, optionally retaining duplicates.

Syntax

Intersect(«Set1», «Set2»[, ALL])

Remarks

Returns the intersection of «Set1» and «Set2». By default, duplicates are eliminated from both sets prior to intersection.

The optional ALL retains duplicates. There are several ways for ALL to work. The algorithm is: Nonduplicated elements are intersected as usual. For each duplicate in «Set1», match it with a duplicate in «Set2», if one exists, and keep matching duplicates in the intersected set.

Example

Intersect({[1994], [1995], [1996]}, {[1995], [1996], [1997]})

returns the set

{[1995], [1996]}.


IsEmpty

Returns TRUE if and only if the evaluated expression is the empty cell value. Returns FALSE otherwise.

Syntax

IsEmpty(«Value Expression»)

Remarks

For more information about empty cells, see the OLE DB documentation.

Example

IsEmpty(Measures.CurrentMember)

returns TRUE if Measures.CurrentMember is an empty cell.


Item

Returns a member from a tuple or a tuple from a set.

 | Members | Tuples
Syntax | «Tuple».Item(«Numeric Expression») | «Set».Item(«String Expression»[, «String Expression»...] | «Index»)
Remarks | Returns the member whose zero-based position in the tuple is specified by «Numeric Expression». | Returns a tuple from «Set». The «String Expression»[, «String Expression»...] specifies which tuple to retrieve. The «Index» value specifies the position of the tuple in «Set». The numbering of the tuples is zero-based.
Example | Time.Year.Members.Item(0) returns [1996] | {([1994],Sales), ([1995],Sales), ([1996],Sales)}.Item(0) returns ([1994],Sales).


Lag

Returns the member that is a specified number of positions prior to a specified member along the member's dimension.

Syntax

«Member».Lag(«Numeric Expression»)

Remarks

Member positions in the dimension are determined by the dimension’s natural order. The numbering of the positions is zero-based.

If «Numeric Expression» is zero, «Member» is returned. If «Numeric Expression» is negative, a subsequent member is returned.

Lag(1) is equivalent to PrevMember. Lag(-1) is equivalent to NextMember.

The function «Member».Lead is similar, except that it looks in the opposite direction. Lag(n) is equivalent to Lead(-n).

Example

If the levels in the Time dimension include Year and Month, then:

[1995].February.Lag(3)

returns [1994].November.


LastChild

Returns the last child of a member.

Syntax

«Member».LastChild

Example

If a Time dimension includes Year, Quarter, Month, Week, and Day, then:

1995.Lastchild

returns December.


LastPeriods

Returns a set of members prior to and including a specified member.

Syntax

LastPeriods(«Index»[, «Member»])

Remarks

If «Member» is not specified, then it is Time.CurrentMember.

If «Index» is positive, returns the set of «Index» members ending with «Member» and starting with the member lagging «Index» - 1 from «Member».

If «Index» is negative, returns the set of (- «Index») members starting with «Member» and ending with the member leading (- «Index» - 1) from «Member».

If «Index» is zero, the empty set is returned.

Examples
Expression | Returns
LastPeriods(2, [1997June]) | {[1997May], [1997June]}
LastPeriods(-2, [1997June]) | {[1997June], [1997July]}
LastPeriods(1, [1997June]) | {[1997June]}
LastPeriods(-1, [1997June]) | {[1997June]}
LastPeriods(0, [1997June]) | Empty set


LastSibling

Returns the last child of the parent of a member.

Syntax

«Member».LastSibling

Example

May.LastSibling

returns June (assuming that the parent level is quarters).


Lead

Returns the member that is a specified number of positions following a specified member along the member's dimension.

Syntax

«Member».Lead(«Numeric Expression»)

Remarks

Member positions in the dimension are determined by the dimension’s natural order. The numbering of the positions is zero-based.

If «Numeric Expression» is zero, «Member» is returned. If «Numeric Expression» is negative, a prior member is returned.

Lead(1) is equivalent to NextMember. Lead(-1) is equivalent to PrevMember.

The function «Member».Lag is similar, except that it looks in the opposite direction. Lead(n) is equivalent to Lag(-n).

Example

If the levels in the Time dimension include Year and Month, then:

[1994].November.Lead(3)

returns [1995].February.


Level

Returns a member's level.

Syntax

«Member».Level

Example

If the Time dimension has levels All, Year, Quarter, Month, Week, and Day, then:

January.Level

returns the Month level.

January.Level.Name

returns the name of the Month level.


Levels

Returns the level whose name is specified by a string expression, or the level whose position in a dimension is specified by a zero-based numeric expression.

String Expressions | Dimension Indexes
Syntax | Levels(«String Expression») | «Dimension».Levels(«Numeric Expression»)
Remarks | Returns the level whose name is specified by «String Expression». Typically used with user-defined functions. | Returns the «Dimension» level specified by «Numeric Expression».
Example Levels(“Year”)
returns the Year level. 		If the Time dimension has levels All, Year, Quarter, Month, Week, and Day, then:

Time.Levels(2)

returns the Quarter level.


LinRegIntercept

Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b.

Syntax

LinRegIntercept(«Set», «Numeric Expression»
[, «Numeric Expression»])

Remarks

Linear regression by using the least-squares method calculates the equation of the best-fit line for a series of points. Let the regression line be given by the equation

y = ax+b

where a is called the intercept and b is called the slope.

This function evaluates «Set» against the first «Numeric Expression» to get the set of values for the y-axis. It then evaluates «Set» against the second «Numeric Expression», if present, to get the set of values for the x-axis. If the second «Numeric Expression» is not present, the function uses the members of «Set» as values for the x-axis.

The latter case is not often useful for standard dimensions (for example, SalesPerson). However, it is often used with the time dimension.

After obtaining the set of points, LinRegIntercept returns the intercept of the regression line (b in the equation above).

Note Empty cells or cells containing text or logical values are ignored; however, cells with zero values are included.

Example

To find the trend in Sales for the sales of the last nine periods:

LinRegSlope(LastPeriods(9), Sales) returns the value for a.

LinRegIntercept(LastPeriods(9), Sales) returns the value for b.


LinRegPoint

Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b.

Syntax

LinRegPoint(«Numeric Expression», «Set», «Numeric Expression»[, «Numeric Expression»])

Remarks

Linear regression by using the least-squares method calculates the equation of the best-it line for a series of points. Let the regression line be given by the equation

y = ax+b

where a is called the intercept and b is called the slope.

LinRegPoint uses the last three arguments, that is «Set» and «Numeric Expression», like the other LinReg xxx functions to calculate the regression line. The function evaluates the first argument «Numeric Expression» and uses the resulting number as the “x” value in the regression equation (y = ax + b) to calculate the “y” value.

Note Empty cells or cells containing text or logical values are ignored; however, cells with zero values are included.

Example

To find the trend in Sales for the sales of the last nine periods and forecast the next period:

LinRegSlope(LastPeriods(9), Sales) returns the value for a.

LinRegIntercept(LastPeriods(9), Sales) returns the value for b.

LinRegPoint(10, LastPeriods(9), Sales) returns the next period.


LinRegR2

Calculates the linear regression of a set and returns R2 (the coefficient of determination).

Syntax

LinRegR2(«Set», «Numeric Expression»[, «Numeric Expression»])

Remarks

Linear regression by using the least-squares method calculates the equation of the best-fit line for a series of points. Let the regression line be given by the equation

y = ax+b

where a is called the intercept and b is called the slope.

This function evaluates «Set» against the first «Numeric Expression» to get the set of values for the y-axis. It then evaluates «Set» against the second «Numeric Expression», if present, to get the set of values for the x-axis. If the second «Numeric Expression» is not present, the function uses the members of «Set» as values for the x-axis.

The latter case is not often useful for standard dimensions (for example, SalesPerson). However, it is often used with the time dimension.

After obtaining the set of points, LinRegR2 returns the statistical R2 that describes the fit of the linear equation to the points.

Note Empty cells or cells containing text or logical values are ignored; however, cells with zero values are included.


LinRegSlope

Calculates the linear regression of a set and returns the value of a in the regression line y = ax + b.

Syntax

LinRegSlope(«Set», «Numeric Expression»[, «Numeric Expression»])

Remarks

Linear regression by using the least-squares method calculates the equation of the best-fit line for a series of points. Let the regression line be given by the equation

y = ax+b

where a is called the intercept and b is called the slope.

This function evaluates «Set» against the first «Numeric Expression» to get the set of values for the y-axis. It then evaluates «Set» against the second «Numeric Expression», if present, to get the set of values for the x-axis. If the second «Numeric Expression» is not present, the function uses the members of «Set» as values for the x-axis.

The latter case is not often useful for standard dimensions (for example, SalesPerson). However, it is often used with the time dimension.

After obtaining the set of points, LinRegSlope returns the slope of the regression line (a in the equation above).

Note Empty cells or cells containing text or logical values are ignored; however, cells with zero values are included.

Example

To find the trend in Sales for the sales of the last nine periods:

LinRegSlope(LastPeriods(9), Sales) returns the value for a.

LinRegIntercept(LastPeriods(9), Sales) returns the value for b.


LinRegVariance

Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b.

Syntax

LinRegVariance(«Set», «Numeric Expression»[, «Numeric Expression»])

Remarks

Linear regression by using the least-squares method calculates the equation of the best-fit line for a series of points. Let the regression line be given by the equation

y = ax+b

where a is called the intercept and b is called the slope.

This function evaluates «Set» against the first «Numeric Expression» to get the set of values for the y-axis. It then evaluates «Set» against the second «Numeric Expression», if present, to get the set of values for the x-axis. If the second «Numeric Expression» is not present, the function uses the members of «Set» as values for the x-axis.

The latter case is not often useful for standard dimensions (for example, SalesPerson). However, it is often used with the time dimension.

After obtaining the set of points, LinRegVariance returns the statistical variance that describes the fit of the linear equation to the points.

Note Empty cells or cells containing text or logical values are ignored; however, cells with zero values are included.


Max

Returns the maximum value of a numeric expression evaluated over a set.

Syntax

Max(«Set»[, «Numeric Expression»])

Example

Max({USA, CANADA, MEXICO}, Sales)

returns 3000 if respective Sales of the countries are 1000, 2000, and 3000.


Median

Returns the median value of a numeric expression evaluated over a set.

Syntax

Median(«Set»[, «Numeric Expression»])

Example

Median({USA, CANADA, MEXICO}, Sales)

returns 2000 if respective Sales of the countries are 1000, 2000, and 3000.


Members

Returns the set of members in a dimension, level, or hierarchy, or the member whose name is specified by a string.

Syntax 1

«Dimension».Members

Remarks 1

Returns the set of all members in «Dimension».

Example 1

Geography.Members

returns the set of all members in the Geography dimension.

Syntax 2

«Hierarchy».Members

Remarks 2

Returns the set of all members in «Hierarchy».

Example 2

Time.Quarterly.Members

returns the set of all members in the Quarters hierarchy of the Time dimension.

Syntax 3

«Level».Members

Remarks 3

Returns the set of all members at a specified level in a dimension.

Example 3

If the Year level contains [1994], [1995], and [1996], then:

Year.Members

returns the set

{[1994], [1995], [1996]}.

Syntax 4

Members(«String Expression»)

Remarks 4

Returns the member whose name is given by «String Expression». Typically used with user-defined functions.

Example 4

Members(UDF())

where UDF() is a user-defined function that returns a string.


Min

Returns the minimum value of a numeric expression evaluated over a set.

Syntax

Min(«Set»[, «Numeric Expression»])

Example

Min({USA, CANADA, MEXICO}, Sales)

returns 1000 if the respective Sales of the countries are 1000, 2000, and 3000.


Mtd

A shortcut function for the PeriodsToDate function that specifies the level to be Month.

Syntax

Mtd([«Member»])

Remarks

A shortcut function to the PeriodsToDate function that defines that function's «Level» argument to be Month. If no member is specified, then the default is Time.CurrentMember.

Mtd(«Member») is the equivalent of PeriodsToDate(Month, «Member»).

Example

MTD()


Name

Returns the name of a level, dimension, member, or hierarchy.

 | Levels | Dimensions | Members | Hierarchies
Syntax | «Level».Name | «Dimension».Name | «Member».Name | «Hierarchy». Name
Remarks | Returns the name of a level. | Returns the name of a dimension. | Returns the name of a member. | Returns the name of a hierarchy.
Example | Products. [Product Description]. Name | Products.Name | Products.[Product Description]. Widget.Name | Time.[Fiscal Year].Name


NextMember

Returns the next member in the level that contains a specified member.

Syntax

«Member».NextMember

Example

If the Year level consists of 1994, 1995, and 1996, then:

[1994].NextMember

returns [1995].


OpeningPeriod

Returns the first sibling among the descendants of a member at a level.

Syntax

OpeningPeriod([«Level»[, «Member»]])

Remarks

The dimension containing «Level» is used if «Level» is specified; otherwise, the Time dimension is used. If no «Level» is specified, the level below that of «Member» is used. If no «Level» or «Member» is specified, the default is Time.CurrentMember.

This function is equivalent to TopCount(Descendants(«Member», «Level»), 1).

The function ClosingPeriod is similar, the only difference being that it returns the last sibling instead of the first sibling.

Example

OpeningPeriod(Month, [1991])

returns [1991].January.


Order

Arranges members of a set, optionally preserving or breaking the hierarchy.

Syntax

Order(«Set», {«String Expression» | «Numeric Expression»}
[, ASC | DESC | BASC | BDESC])

Remarks

There are two varieties of Order: hierarchized (ASC or DESC) and nonhierarchized (BASC or BDESC where “B” indicates “Break hierarchy”). The hierarchized ordering first arranges members according to their position in the hierarchy. Then it orders each level. The nonhierarchized ordering arranges members in the set without regard to the hierarchy. In the absence of an explicit specification, ASC is the default.

Example

Order(SampleSet, ([1995], Sales), DESC)

hierarchizes all members and sorts each level according to Sales. Sales are compared at the highest level when the sorted list is constructed. Therefore, if the sum of Sales in all California cities is less than the sum of Sales in all New York cities, California and California.LA will appear below NYC in the sorted, descending list.

The result of

Order(SampleSet, ([1995], Sales), DESC)

is as follows:

Location |  |  | 1995 sales
USA |  |  | 5000
 | California |  | 2000
 |  | LA | 500
 |  | Buffalo | 300
 |  | NYC | 900
France |  |  | 2500
 |  | Paris | 365
 |  | Nice | 27
UK |  |  | 1900
 |  | London | 250

Order(SampleSet, ([1995], Sales), BDESC)

sorts the members according to their values without regard for their relative positions in the member hierarchy. In this example, numeric values are sorted by 1995 sales per city, including aggregate sales values by state and country.

The result of

Order(SampleSet, ([1995], Sales), BDESC)

is as follows:

Location | 1995 sales
USA | 5000
France | 2500
California | 2000
UK | 1900
NYC | 900
LA | 500
Paris | 365
Buffalo | 300
London | 250
Nice | 27

Note When the input set has two elements for which the «String Expression» or «Numeric Expression» has the same value, the input order is preserved.

For example if the sales for USA & Europe is 300 each, and that for Asia is 100, then the call:

Order({USA, Europe, Asia}, Sales, BASC)

returns the set

{Asia, USA, Europe}

and not the set

{Asia, Europe, USA}.


Ordinal

Returns the zero-based ordinal value associated with a level.

Syntax

«Level».Ordinal

Example

Products.[Product Description].Ordinal


ParallelPeriod

Returns a member from a prior period in the same relative position as a specified member, for example the same relative month in the previous quarter.

Syntax

ParallelPeriod([«Level»[, «Numeric Expression»[, «Member»]]])

Remarks

This function is similar to the Cousin function, but is more closely related to Time series. It takes the ancestor of «Member» at «Level» (call it ancestor); then it takes the sibling of ancestor that lags by «Numeric Expression», and returns the parallel period of «Member» among the descendants of that sibling.

This function has the following defaults:

Default «Member» value is Time.CurrentMember if «Level» is not specified. Otherwise it is dimension.CurrentMember, where dimension is the dimension to which level belongs.

Default «Numeric Expression» is 1.

Default «Level» is the level of the parent of «Member».

This function is equivalent to:

Cousin(Member,Lag(Ancestor(Member,Level),Numeric Expression)

Example
Expression | Returns
PARALLELPERIOD(Year,2, [96 Qtr 3]) | [94 Qtr 3]
PARALLELPERIOD(Year,2) | The parallel period of Time.CurrentMember, two years ago. That is, if Time.CurrentMember is [1993June], then the returned member is [1991June].
PARALLELPERIOD(Year) | The parallel period of Time.CurrentMember from last year. That is, if Time.CurrentMember is [1993June], then the returned member is [1992June].
PARALLELPERIOD() | The parallel period in the immediately prior sibling to the parent of Time.CurrentMember. For example, if Time.CurrentMember is [1993June], then the returned member is [1993March]. The parent of ([1993June] is Quarter2, whose immediately prior sibling is Quarter1, in which the parallel period is [1993March].


Parent

Returns the parent of a member.

Syntax

«Member».Parent

Example

If the Geography dimension includes State and Country, then:

California.Parent

returns USA.


PeriodsToDate

Returns a set of periods (members) from a specified level starting with the first period and ending with a specified member.

Syntax

PeriodsToDate([«Level»[, «Member»]])

Remarks

Within the scope of «Level», returns the set of periods on the level of «Member», starting with the first period and ending with «Member». If no level or member is specified, then the «Member» value is Time.CurrentMember and «Level» is the parent level of Time.CurrentMember. If a level is specified, then «Member» is dimension.CurrentMember, where dimension is the dimension of «Level».

Example
Expression | Returns
PeriodsToDate(Quarter, [05-Sep-1997]) | The set of days from the beginning of Quarter3.
PeriodsToDate(Year, March) | The set {January, February, March}.
PeriodsToDate(Year) | The set of members from the beginning of the year that is the ancestor of Time.CurrentMember, through Time.CurrentMember.
PeriodsToDate() | The set of members from the beginning of the containing period of Time.CurrentMember to Time.CurrentMember. All the returned members are at the same level as Time.CurrentMember.

PeriodsToDate(level, member) is the same as:

TopCount(Descendants(Ancestor(member, level), member.Level), 1):member


PrevMember

Returns the previous member in the level that contains a specified member.

Syntax

«Member».PrevMember

Example

If the Year level consists of [1994], [1995], and [1996], then:

[1996].Prevmember

returns [1995].


Qtd

A shortcut function for the PeriodsToDate function that specifies the level to be Quarter.

Syntax

Qtd([«Member»])

Remarks

A shortcut function to the PeriodsToDate function that predefines that function's «Level» argument to be Quarter. If no member is specified, then the default is Time.CurrentMember.

Qtd(«Member») is the equivalent of PeriodsToDate(Quarter, «Member»).

Example

Qtd()


Rank

Returns the one-based rank of a tuple in a set.

Syntax

Rank(«Tuple», «Set»)

Example

Rank((c,d), {(a,b), (c,d), (e,f)})

returns 2.


SetToArray

Converts one or more sets to an array for use in a user-defined function.

Syntax

SetToArray(«Set»[, «Set»]...[, «Numeric Expression»])

Remarks

Converts one or more sets to an array for use in a user-defined function. The number of dimensions in the resulting array is the same as the number of sets specified.

The optional numeric expression can be used to provide the values in the array cells. If omitted, the default value of the set member is used for the array cell value.

The cell coordinates in the resulting array correspond to the position of the sets in the list. For example, SetToArray(SA, SB, SC), where each set has two elements, produces the three-dimensional array:

(SA1, SB1, SC1) (SA2, SB1, SC1) (SA1, SB2, SC1) (SA2, SB2, SC1)

(SA1, SB1, SC2) (SA2, SB1, SC2) (SA1, SB2, SC2) (SA2, SB2, SC2)

Example

SetToArray(Geography.Members, Measures.Sale)


SetToStr

Constructs a string from a set.

Syntax

SetToStr(«Set»)

Example

SetToStr({1995, 1996})

returns

"{[Time].[1995], [Time].[1996]}".


Stddev

Alias for Stdev.


StddevP

Alias for StdevP.


Stdev

Returns the standard deviation of a numeric expression evaluated over a set (unbiased).

Syntax

Stdev(«Set»[, «Numeric Expression»])

Note Stdev uses the unbiased population formula. StdevP uses the biased population formula.

Example

Stdev({USA, CANADA, MEXICO}, Sales)


StdevP

Returns the standard deviation of a numeric expression evaluated over a set (biased).

Syntax

StdevP(«Set»[, «Numeric Expression»])

Note StdevP uses the biased population formula. Stdev uses the unbiased population formula.

Example

Stdev({USA, CANADA, MEXICO}, Sales)


StripCalculatedMembers

Removes calculated members from a set.

Syntax

StripCalculatedMembers(«Set»)

Remarks

Removes calculated members from a set that includes calculated members added with AddCalculatedMembers.


StrToSet

Constructs a set from a string expression.

Syntax

StrToSet(«String Expression»)

Example

StrToSet("Time.Members")

returns a set containing all the members of the Time dimension.


StrToTuple

Constructs a tuple from a string expression.

Syntax

StrToTuple(«String Expression»)

Example

StrToTuple("(Sales, [1994])")

returns (Sales, [1994]).


Subset

Returns a subset of elements from a set.

Syntax

Subset(«Set», «Start»[, «Count»])

Remarks

Returns «Count» elements from «Set», starting at position «Start». «Start» is zero-based: 0 corresponds to the first element in the set, 1 corresponds to the second, and so on. If «Count» is not specified, all elements from «Start» to the end of the set are returned.

Example

Subset({USA, Canada, France, Germany, Japan, England, Peru}, 0, 2)

returns the set

{USA, Canada}.


Sum

Returns the sum of a numeric expression evaluated over a set.

Syntax

Sum(«Set»[, «Numeric Expression»])

Example

Sum({USA, CANADA, MEXICO}, Sales.VALUE)

returns 6000 if their respective Sales are 1000, 2000, and 3000. This can also be written in a more intuitive way as

Sum({USA, CANADA, MEXICO}, Sales).


Tail

Returns a subset from the end of a set.

Syntax

Tail(«Set»[, «Count»])

Remarks

Returns the last «Count» elements in a set. The order of elements is preserved. The default value of «Count» is 1. If «Count» is less than 1, the empty set is returned. If «Count» exceeds the number of tuples in the set, the original set is returned.

Example

Tail({USA, Canada, France, Germany, Japan}, 3)

returns the set

{France, Germany, Japan}.


ToggleDrillState

Toggles the drill state of members. This function is a combination of DrillupMember and DrilldownMember.

Syntax

ToggleDrillState(«Set1», «Set2»[, RECURSIVE])

Remarks

Toggles the drill state of each member of «Set2» that is present in «Set1». If a member m of «Set2» that is present in «Set1» is drilled down (that is, has a descendant), DrillupMember(«Set1», {m}) is applied. If it is drilled up (that is, there is no descendant of m that immediately follows m), DrilldownMember(«Set1», {m}[, RECURSIVE]) is applied to Set1. The optional RECURSIVE flag is used if ToggleDrillState was called with RECURSIVE.


TopCount

Returns a specified number of items from the top of a set, optionally ordering the set first.

Syntax

TopCount(«Set», «Count»[, «Numeric Expression»])

Remarks

Sorts a set according to the value of «Numeric Expression» and returns the top «Count» members, where «Count» is a numeric expression.

This function always breaks the hierarchy. The BottomCount function is similar.

Example

Topcount(Geography.Cities.Members, 5, Sales)


TopPercent

Sorts a set and returns the top N elements whose cumulative total is at least a specified percentage.

Syntax

TopPercent(«Set», «Percentage», «Numeric Expression»)

Remarks

Sorts a set using «Numeric Expression» and returns the top N elements whose cumulative total of «Numeric Expression» is at least «Percentage». «Percentage» is a numeric expression.

This function always breaks the hierarchy. The BottomPercent function is similar.

Example

TopPercent({London, Paris, Rome, New York, Seattle, Tokyo}, 15, Sales)


TopSum

Sorts a set and returns the top N elements whose cumulative total is at least a specified value.

Syntax

TopSum(«Set», «Value», «Numeric Expression»)

Remarks

Sorts on «Numeric Expression» and picks up the top N (the smallest number possible) elements such that their sum is at least «Value».

This function always breaks the hierarchy. The BottomSum function is similar.

Example

Topsum(Products.[Product Description].Members, 100000, Quantity)


TupleToStr

Constructs a string from a tuple.

Syntax

TupleToStr(«Tuple»)

Example

TupleToStr(France, 1995)

returns

"([Time].[1995], [Regions].[All Regions].[Europe].[France])".


Union

Returns the union of two sets, optionally retaining duplicates.

Syntax

Union(«Set1», «Set2»[, ALL])

Remarks

Returns the union of «Set1» and «Set2», eliminating duplicates by default. The ALL flag keeps duplicates in the joined set. Duplicates are deleted from the tail.

Alternate Syntax
(Implicit Union)

{Set1, Set2} or «Set1» + «Set2»

Other ways of joining by union is to use a comma-separated list of sets within braces or to use the + operator. For example,

1. {USA.Children, CANADA.Children}

2. {USA.Children} + {CANADA.Children}

are equivalent to

Union(USA.Children, CANADA.Children, ALL).

Duplicated members are always retained when an alternate syntax is used.

Note This alternate syntax is a Microsoft® SQL Server™ OLAP Services multidimensional expression (MDX) extension to OLE DB.


UniqueName

Returns the unique name of a level, dimension, or member.

 | Levels | Dimensions | Members
Syntax | «Level».UniqueName | «Dimension». UniqueName | «Member». UniqueName
Remarks | Returns the unique name of a level. | Returns the unique name of a dimension. | Returns the unique name of a member.
Example | Products.[Product Description]. Uniquename | Products.Uniquename | Products.[Product Description].Widget. Uniquename


ValidMeasure

Returns a valid measure in a virtual cube by forcing inapplicable dimensions to their top level.

Syntax

ValidMeasure(«Tuple»)

Remarks

When computing values in a virtual cube, measure cells only contain values at the (All) level for dimensions that are not common between the underlying cubes. The ValidMeasure function returns the measure value from the cell at the (All) level coordinates for the dimensions that are not common.

Example

Consider a virtual cube named ExchSales derived from cubes Sales and ExchRate. The Sales cube contains dimensions Time, Products, and Customers, and the measure Sale in dollars. The ExchRate cube contains dimensions Time and Currency, and the measure Rate.

To create a calculated member YenSale that returns the value of each sale in Yen, the (All) level must be specified for each dimension not present in the ExchRate cube for there to be a value present in the virtual cube cell. This can be cumbersome for virtual cubes where a number of dimensions are not common between the underlying cubes.

The ValidMeasure function ensures the measure's value is taken from the appropriate cell at the (All) level for each dimension not in common. The following two MDX expressions are equivalent:

YenSale = Sum({Descendants(Time.CurrentMember, Day}},
   Sale * (Currency.Yen, AllProducts, AllCustomers))

YenSale = Sum({Descendants(Time.CurrentMember, Day}},
   Sale * ValidMeasure(Currency.Yen))


Value

Returns the value of a measure. Value is the default property of a measure.

Syntax

«Member».Value

Example

Sales.Measures.CurrentMember.Value


Var

Returns the variance of a numeric expression evaluated over a set (unbiased).

Syntax

Var(«Set»[, «Numeric Expression»])

Note Var uses the unbiased population formula. VarP uses the biased population formula.

Example

Var ({USA, CANADA, MEXICO}, Sales)


Variance

Alias for Var.


VarianceP

Alias for VarP.


VarP

Returns the variance of a numeric expression evaluated over a set (biased).

Syntax

VarP(«Set»[, «Numeric Expression»])

Note VarP uses the biased population formula. Var uses the unbiased population formula.

Example

VarP({USA, CANADA, MEXICO}, Sales)


VisualTotals

Dynamically totals child members of parent member specified in a set using a pattern for the total label in the result set.

Syntax

VisualTotals(«Set», «Pattern»)

Remarks

This function totals the values of the child members specified in «Set» only. Child members not specified in «Set» will not be included in the result. «Pattern» specifies the format for the totals label. Text for the pattern is taken literally and the asterisk (*) is the substitution character for the parent member. To display a literal asterisk, use two asterisks (**).

Note The VisualTotals function replaces the parent member of the resulting cell set. Multiple hierarchies of Parent and Child members may be specified in «Set».

Example

Assume that the Product dimension has the level Baked Goods with a child of Bread. Bread has the members Bagels, Muffins, and Sliced Bread.

In the first case, a select statement is done using

[Product].[All Products].[Food].[Baked Goods].[Bread]

as the parent member and ...[Bagels] and ...[Muffins] for the child members. The results for Bread reflect the precalculated values of all of the children of [Bread] and do not take into account that the Sliced Bread member has not been included in the resulting set.

select

  {[Measures].[Unit Sales]} on columns,

  {[Product].[All Products].[Food].[Baked Goods].[Bread],

   [Product].[All Products].[Food].[Baked Goods].[Bread].[Bagels],

   [Product].[All Products].[Food].[Baked Goods].[Bread].[Muffins]

  } on rows

from Sales

 | Unit Sales
 | 7,870.00
Bagels | 815.00
Muffins | 3,497.00

An alternate solution is to use the VisualTotals function to dynamically total the child members in the set and display an accurate value for Bread.

select

{[Measures].[Unit Sales]} on columns,

{VisualTotals({[Product].[All Products].[Food].[Baked Goods].[Bread],

  [Product].[All Products].[Food].[Baked Goods].[Bread].[Bagels],

  [Product].[All Products].[Food].[Baked Goods].[Bread].[Muffins]},

   "**SubTotal - *")

} on rows

from Sales

 | Unit Sales
*Subtotal - Bread | 4,312.00
Bagels | 815.00
Muffins | 3,497.00

The string “*SubTotal - Bread” is constructed by substituting the single asterisk substitution character with “Bread” to produce a meaningful name for the dynamically calculated total. The output asterisk is specified by the double asterisks in the substitution string.


Wtd

A shortcut function for the PeriodsToDate function that specifies the level to be Week.

Syntax

Wtd([«Member»])

Remarks

A shortcut function to the PeriodsToDate function that defines that function’s «Level» argument to be Week. If no member is specified, then the default is Time.CurrentMember.

Wtd(«Member») is the equivalent of PeriodsToDate(Wtd, «Member»).

Example

Wtd()


Ytd

A shortcut function for the PeriodsToDate function that specifies the level to be Year.

Syntax

Ytd([«Member»])

Remarks

A shortcut function to the PeriodsToDate function that defines that function’s «Level» argument to be Year. If no member is specified, the default is Time.CurrentMember.

Ytd(«Member») is the equivalent of PeriodsToDate(Year, «Member»).

Example

Ytd()


Select the Parent Member Dialog Box

This dialog box enables you to select a different parent member for a calculated member. The parent member determines the location of the calculated member in the dimension structure.

This dialog box appears when you click Change in the Calculated Member Builder.

Options

Tree view

Enables you to expand the dimension and select the parent member you want for the calculated member.

OK

Selects the parent member you choose and returns you to the Calculated Member Builder.

Cancel

Cancels the selection of a parent member and returns you to the Calculated Member Builder.

Help

Displays this topic in Help.


Register Function Libraries Dialog Box

This dialog box enables you to register other products’ function libraries or user-defined function libraries.

This dialog box is displayed when you click Register in the Calculated Member Builder.

You can create user-defined functions to provide functions not in the Microsoft® SQL Server™ OLAP Services function library. A user-defined function can be developed on any tool capable of generating Microsoft ActiveX® libraries.

You can register user-defined function libraries of the following types:

  • Type libraries (*.olb, *.tlb, *.dll)
  • Executable files (*.exe, *.dll)
  • ActiveX controls (*.ocx)

The OLAP Services functions always take precedence over registered function libraries when function name conflicts occur (that is, two libraries contain the same function name). Except OLAP Services functions, the order of the libraries in the list is important. It defines the precedence of function resolution in cases of name conflicts. The first library takes precedence.

OLAP Services automatically registers the Visual Basic® for Applications Expression Services function library (VBA332.dll) and the library that supports the Microsoft Excel worksheet functions. However, the Excel library must be installed separately from OLAP Services. For lists of supported functions, see “Visual Basic for Applications Functions” and “Excel Functions,” both in OLAP Services Books Online (msdssbol.chm).

See Also

Using the Register Function Libraries Dialog Box

Calling a User-defined Function within MDX


Using the Register Function Libraries Dialog Box

In this dialog box, you can perform the following procedures:

To add a function library

  1. Click Add.
  2. Locate and select the library file you want to add.

The program IDs associated with the function library appear in the list box.

To remove a program ID

  • Select the program ID, and then click Remove.

To complete registration

  • When you are satisfied that the correct programs in external function libraries are contained in the list box, click Close.

The program IDs of the libraries now appear in the Functions box of the Calculated Member Builder. You can return to the Register Function Libraries dialog box and add or remove other libraries.

Note Functions are supported only if they accept as arguments only string or numeric data types, or array or variant data types containing string or numeric values. In addition, functions are supported only if they return only string or numeric data types, or variant data types containing numeric values.

See Also

Calling a User-defined Function within MDX


Calling a User-defined Function within MDX

After a user-defined function is registered in the Register Function Libraries dialog box, it can be used anywhere in the multidimensional expressions (MDX) syntax that allows expressions. For example:

With Member Measures.[Forecasted Sales] As

     'Sales * ForecastedGrowthRate(SaleReps.CurrentMember.Name)'

Select TopCount(SalesReps, HowManyReps(), Sales) on Rows,

     {Sales, [Forecasted Sales] } on Columns

From Sales

The HowManyReps and ForecastedGrowthRate user-defined functions are defined as:

Public Function HowManyReps() as Integer

Public Function ForecastedGrowthRate(RepName as String) as Double

Note There may be multiple user-defined functions residing in the same Microsoft® ActiveX® library.

See Also

Calculated Member Builder

Using the Register Function Libraries Dialog Box


Copy and Paste Objects Add-in

The Copy and Paste Objects Add-in enables you to use the OLAP Manager to copy and paste many types of objects used in Microsoft® SQL Server™ OLAP Services. The add-in provides Copy and Paste options on the following menus:

  • The shortcut (that is, right-click) menus in the OLAP Manager tree view
  • The OLAP Manager Action menu

The Copy operation copies the selected object into a buffer. A subsequent Paste operation reads the buffer and copies its contents to the selected location. Each Copy operation replaces the buffer contents. A Paste operation copies only the current buffer contents created by the most recent Copy operation.

You can use the add-in to copy and paste objects within a database, between databases, and between servers.

To copy and paste an object

  1. In the OLAP Manager tree view, right-click the object, and then click Copy.
  2. Right-click the target location for the new object, and then click Paste.

Copy is not available for all object types. Paste is not available if you select a target location that is invalid for the object type you have copied.

Note This add-in copies only metadata. It does not copy data such as aggregations, source data, and write-back tables. This data must be available to some newly pasted objects. To ensure this availability, after pasting follow these steps:

  1. Make sure data sources at the target location are defined correctly and connected so that the newly pasted objects have access to source data. This requirement is especially important after copying and pasting objects between servers.
  2. Process newly pasted objects of types that require processing.

You must copy and paste within a single instance of the OLAP Manager. It is not possible to copy an object within one instance of the OLAP Manager and then paste it within another instance.

If you attempt to paste an object to a location that already contains an object of the same type and name, you are prompted to supply a different name for the new object you are creating.

When some types of objects are copied, supporting objects are copied with them. For example, a cube contains dimensions, measures, one or more partitions, and a data source. A cube may also contain other objects. It may also rely on other objects such as shared dimensions and roles that exist outside the cube. All of these objects are the cube’s supporting objects, and they are copied along with the cube. (If a supporting object has its own supporting objects, they are also copied.) When you paste an object that was copied with supporting objects, the supporting objects are pasted along with the main object. Exceptions are:

  • Virtual cubes. Before you copy and paste a virtual cube to a different database, you must copy and paste to that database the component cubes on which the virtual cube is based. To determine these component cubes, in the OLAP Manager tree view, right-click the virtual cube, click Edit, and see the Virtual cube includes box.
  • Virtual dimensions. Before you copy and paste a virtual dimension to a different database, you must copy and paste to that database the shared dimension that contains the member property on which the virtual dimension is based. To determine this shared dimension, in the OLAP Manager tree view, right-click the virtual dimension, click Edit, and see the parent dimension of the preselected member property.
  • The situation in which the target location already contains an object with the same type and name as a supporting object but a different structure than the supporting object. In this case, the paste operation fails, and no objects are pasted. If objects already exist in the target location with the same types and names as the supporting objects and the same structures as the supporting objects, the paste of the main object is successful, but the supporting objects are not pasted.

If you paste a ROLAP partition (or an object that contains a ROLAP partition), the original ROLAP partition and its copy will use the same aggregation tables. This situation may cause overwrite conflicts in the tables. For example, if you change and process the original partition, the aggregation tables may no longer be valid for its copy. To avoid this problem, immediately after pasting specify a unique aggregation prefix for the pasted ROLAP partition and process it. This action creates new aggregation tables used by only the pasted partition. To access the aggregation prefix for a partition, in the OLAP Manager tree view, right-click the partition, click Edit, advance to the final step of the Partition wizard, and then click Advanced.